Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am having KPI having following condition
KPI:
if (sum([Quantity]) <> 0,
if (Sum([DailyQuantitySales]) <> 0,
sum([DailySales]) - sum([DailyVarSales]),
sum([DailySales])
)
,0)
Bar Chart : Region as dimension
Measure:
if (sum([Quantity]) <> 0,
if (Sum([DailyQuantitySales]) <> 0,
sum({<[Region] ={"=Sum([margin])>=0"}+{"=Sum([margin])<0"} >}[DailySales]) - sum({<[Region] ={"=Sum([margin])>=0"}+{"=Sum([margin])<0"} >}[DailyVarSales]),
sum({<[Region] ={"=Sum([margin])>=0"}+{"=Sum([margin])<0"} >}[DailySales])
)
,0)
What could be the reason for getting total of KPI is not matching with total of Bar chart
Try this for KPI
Sum(Aggr(
If(Sum([Quantity]) <> 0,
If(Sum([DailyQuantitySales]) <> 0,
Sum([DailySales]) - Sum([DailyVarSales]),
Sum([DailySales])), 0)
, Region))
Edit: Also, check here to see why we need this
If i use the AGGR then my result will be for that dimension based
but i need a result which is applicable to all the dimension not only specific to region in KPI .
I can have the Other Dimention like Country and Customer etc
So, each dimension gives a different result, right? How do you expect a dimensionless KPI to know which dimension do you want it to evaluate the if statement on?
No my doubt was i am not sure what dimension client can use for selection .
Lets assume user select country dimension value and my KPI is having this expression .
Whether Country Wise KPI value will be equal to Region wise ? Whether This expression will give a correct result
?
Sum(Aggr(
If(Sum([Quantity]) <> 0,
If(Sum([DailyQuantitySales]) <> 0,
Sum([DailySales]) - Sum([DailyVarSales]),
Sum([DailySales])), 0)
, Region))
It won't... but that is because the KPI needs to be programmed ahead to give the matching dimension. How do you let the user pick dimension?
If you were using this as variable, it could potentially used in a KPI or bar graph measure, where the bar graph has alternate dimensions to slice and dice.
In that case, it *may* work depending on dimensions, as in this case where regions make up countries, then just add the dimensions to the aggr()
I've also used the qs_Variable extension on Branch to allow the user to select the dimension, then you would replace the aggr dimension with your variable value.