Bar chart to show Percent

Q&A
, , ,
1

Dataset is {CustId, Year, Amount}
Filtering is by 1 single CustId

image
image316×167 11.6 KB

Using Pie Chart (Group/Color is Year and Value is sum(Amount) ), % is provided out of the box.

Using horizontal bar chart (y-axis is Year, Value is sum(Amount) ), % is not available.

image
image357×116 2.61 KB

Calculated field of formula “Amount / sumOver(Amount, [CustId], PRE_AGG)” to get the percentage is incorrect. “sum(Amount) / sumOver(Amount, [CustId], PRE_AGG)” will give error “Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.”

How to get % on bar chart?

2

Hi @lbl,
It looks like you’ll want to account for the year in your aggregation as well based on your visuals. Can you try the following and let me know if this works for your case:

sumOver({Amount}, [{CustId}, {Year}], PRE_AGG)/sumOver({Amount}, [{CustId}], PRE_AGG)

You’re receiving the mismatched aggregation error because you’re trying to use a ‘sum’ with a ‘sumOver’ so you’ll want to use ‘SumOver’ for both.

3

Hi @Brett
Brillant! The formula works perfectly.
Thanks!