How to create Line Chart based on a category AVE value?

Harry Sun

Hi Board Team,

I have created a table like following. I collapsed the table so I can see the Row Total only, which is calculated based on Product AVE.

When I use a table with a category, it is by Row by Channel L4 and Product. So, it is calculated at each row first, then make the average from each row calculation.

If I add Channel L4 and Products to the line chart Category, it will make the graph looks strange.

But if I only use the Channel L4, the number is different. I think it is aggregating the number to a total first, then calculate the AVG (rather than calculate at Product level by row, then AVG each row).

How can I make the line same as the Table showing AVG by Channel L4 and Product? Thanks

Remo Bollhalder

Hi @Harry Sun

The Average Summary is calculated based on the dimensions in your data view / chart. Without the Product Dimension you will not get the same numbers as in your data view.

Something you could try is to use the same configuration as in your data view in the chart and activate the "Switch Row/Column" Option. It's not possible to collapse the data, but you can set the individual Products to invisible to just show the Channel L4 averages.

Best,

Remo

Harry Sun

Hi Remo,

I need to keep Month at CATEGORIES AXIS, so the month is month is on X Axis. But I cannot add Product to Series because series does not support multiple column entities.

Harry Sun

Hi Remo,

I tried this again by using Channel L4 and Product at CATEGORIES and Month at Series. Then I Switch Row/Columns. It works ok.

But I have around 100 products. Instead of making each product invisible, is their any quick way I can make individual items invisible?

Thanks

Remo Bollhalder

Hi Harry,

set all series to invisible and then select the channels you would like to see to visible again.

If there are too many data points generally, there is always the possibility of calculating the average into a new cube and displaying the cube on a channel level instead.

Harry Sun

Thanks a lot Remo. I am still learning how to calculate and put data into a new cube. I think it is best way to solve this.

One last question.

When you mention "set all series to invisible and then select the channels", could you please take a snapshot of the screen where I can do this?

Thanks a lot for your prompt reply.

Regards

Remo Bollhalder

Happy to help. You can select all series under the series option:

Make sure you have totals enabled in the chart configuration, otherwise you will not see the total series on the individual channels.

Remo Bollhalder

Invisible might have been the wrong wording, what I meant is you can set the series color to transparent.

Harry Sun

I can select All or each individual series from this dropdown. But how can I set them to invisible? I did not see a checkbox or selection for Visible vs Invisible.

Thanks