Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to create a KPI chart that will show the potential savings opportunity that exists. I have created the master measure ("Potential Savings") that relies on another Master Measure ("Average Cost") and it's displaying correctly when I create a table.
When I create a KPI chart and add it, I do not get an an option to add an aggregate function (Screenshot attached).
I read somewhere that nesting Master items can cause issues, so in the expression I just typed in the expression of the "Average Cost" master measure, but still no success.
Lastly, I tried manually doing the Sum aggregation, but it says it cannot accept nested Aggregations.
I solved this by calculating the necessary variable in the data load script. The issue seemed to be because I was including a dimension in the measure calculation.
Can you share the expression you are using ?
The master expression is this:
if(([Var3]<(Sum({$<[Var1]={">0"}>}[Var2])/sum({<[Var1]={">0"}>}[Var1]))),
(Sum({$<[Var1]={">0"}>}[Var2])/sum({<[Var1]={">0"}>}[Var1])
- [Var3]) * Var4,0)
The sum block is a separate measure, so I've also tried:
if(([Var3]<([Average Cost])),([Average Cost] - [Var3]) * Qty,0)
I solved this by calculating the necessary variable in the data load script. The issue seemed to be because I was including a dimension in the measure calculation.