Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am getting wrong total for overall score. This expression is working fine but i am getting wrong total. I am attaching qvw file for reference. What's the issue with this?
if(Variance_Type='has not',(Sum([Overall Step Weightage])*Sum({<KPI={'WOW'}>}[Numeric Field Weightage]))*100,
if(Variance_Type='has',
if((1-[Overall Step Weightage])<=sum({<KPI={'WOW'}>}[Variance]),(Sum({<KPI={'WOW'}>}[Numeric Field Weightage])*1)*100,
if((1-[Overall Step Weightage])<=sum({<KPI={'Focus'}>}[Variance]),(Sum({<KPI={'WOW'}>}[Numeric Field Weightage])*0.75)*100,
if((1-[Overall Step Weightage])<=sum({<KPI={'Par'}>}[Variance]),(Sum({<KPI={'WOW'}>}[Numeric Field Weightage])*0.5)*100,
if((1-[Overall Step Weightage])<=sum({<KPI={'Marginal'}>}[Variance]),(Sum({<KPI={'WOW'}>}[Numeric Field Weightage])*0.25)*100,
Sum({<KPI={'WOW'}>}[Numeric Field Weightage])*0))))))
Try this
Sum(Aggr(if(Variance_Type='has not',(Sum({<KPI={'WOW'}>}[Numeric Field Weightage])*(Sum(Aggr(Avg({<year_month_num = {$(=Max(year_month_num))}>}[Overall Step Weightage]), Project, Month)))*100),if(Variance_Type='has',if((1-[Overall Step Weightage])<=sum({<KPI={'WOW'}>}[Variance]),(Sum({<KPI={'WOW'}>}[Numeric Field Weightage])*1)*100,
if((1-[Overall Step Weightage])<=sum({<KPI={'Focus'}>}[Variance]),(Sum({<KPI={'WOW'}>}[Numeric Field Weightage])*0.75)*100,
if((1-[Overall Step Weightage])<=sum({<KPI={'Par'}>}[Variance]),(Sum({<KPI={'WOW'}>}[Numeric Field Weightage])*0.5)*100,
if((1-[Overall Step Weightage])<=sum({<KPI={'Marginal'}>}[Variance]),(Sum({<KPI={'WOW'}>}[Numeric Field Weightage])*0.25)*100,Sum({<KPI={'WOW'}>}[Numeric Field Weightage])*0)))))), [Measure/Department], Project))
@Kushal_Chawda please have a look
I am getting 0 in all rows by wrapping expression using aggr function.
SUM(
AGGR(
copypasteyourexpressionhere,
Dim1,Dim2,Dim3 // All of your dimensions
)
)
@sadiaasghar Can you provide one example? What is wrong and what is expected?
@Kushal_Chawda as you can see in qvw file if one measure like health, safety and environment is selected and month sep 2020 is selected. Total should be sum of all rows of overall score in that particular measure ,' health, safety and environment' currently it's using formula to calculate total also. I don't want my expression formuka to be applicable for total.
@sadiaasghar Sorry but still not clear. Can you tell me which number is wrong and what actually number should be?
@Kushal_Chawda Please find attached expected and current output in attachment below
https://drive.google.com/file/d/1d3nOQwLsbanNSz-ghdRvG3kt8pUbd_oT/view?usp=sharing
What i want is that my expression formula should not apply for the total or partial sum. But Total should be equal to sum of all rows.
@Kushal_Chawda What i want is that my expression formula should not apply for the total or partial sum. But Total should be equal to sum of all rows.