Drilldown to show sum of measures value

[riccardoprade93]

Hi guys,

I think this problem is quite common, but I could not find an easy guide to solve it.

I have a forecast of X and actuals of Y. I caculated the ABS ERROR (ABS(Forecast-Demand)).

What I would like to happen is that the upper level of my most disaggregated information would be the sum of all those ABS ERROR and not the variance at the aggregated level.

How I calculated so far the 3 mesures:

Forecast = calculate(sum(forecast)) --> Forecast 1

Demand = calculate(sum(demand)) --> Actuals1

abserror = abs(forecast-demand) --> ABS1

So here below at the upper level, row = PR208 the ABS 1  should be the sum of all the below calculation, the value should be >100k+

Thanks everyone!!!!!!!!

[Ashish_Mathur]

Hi,

Try this measure

Measure = SUMX(VALUES('Table'[Sub_Sales]),[ABS1])

Hope this helps.

[riccardoprade93]

It works for the upper level, but it will not calculate correctly at the lower level, it is a good trick though.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[riccardoprade93]

https://www.dropbox.com/s/mcmfrb2dc2bjhor/Test.pbix?dl=0 

[Ashish_Mathur · Accepted solution]

Hi,

This measure works

ABS3 = if(hasonevalue('Dataset Agg'[SHIP_FROM]),[ABS 1],SUMX(VALUES('Dataset Agg'[SHIP_FROM]),[ABS 1]))

Hope this helps.

[riccardoprade93]

one last comment, can you explain me possibly why if I aggregate one level more, the math does not work?

[Ashish_Mathur]

Hi,

Try this measure

Measure = SUMX(SUMMARIZE('Dataset Agg','Dataset Agg'[SHIP_FROM],'Dataset Agg'[U_SUB_DIV],'Dataset Agg'[Period],"ABCD",[ABS 1]),[ABCD])

[riccardoprade93]

ah this works perfectly! Thanks so much... If you have the time, could you help me understanding the formula?

Why HASNOVALUE?

[v-kkf-msft]

Hi @riccardoprade93 ,

Please try the measure. 'Table'[Sub_Sales] is the field to which "74808.61R/1110", "Y60NAOH48.91R/1980", etc. belong.

ABS1 = 
SUMX (
    VALUES ( 'Table'[Sub_Sales] ),
    CALCULATE ( ABS ( [Forecast 1] - [Actuals1] ) )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[riccardoprade93]

Hey! thanks a lot, but somehow the formula does not replicate in my PowerBI the result that you have.

It will show the sum of the ABS ERROR as well for the lower level. I tried to check for any error, but could not find any. Any idea??

ABS3 =

SUMX (

VALUES ('Dataset Agg'[SHIP_FROM] ),

CALCULATE ( ABS ( [Forecast 1]- [Actuals 1] ) )

)