Measure to Sum the Lower Levels.

Anything related to PowerPivot and DAX Formuale
lalaisgongon
Posts: 3
Joined: Wed Jan 31, 2018 12:36 pm

Measure to Sum the Lower Levels.

Postby lalaisgongon » Wed Jan 31, 2018 12:41 pm

Dear all
Could i seek your advice on the below

Thanks in advance!

I cant seem to get my Lower level calculation to sum up at the lower level, it is now calculating at every level.

My sample file is available on https://drive.google.com/open?id=1lehGxqQG2vE_D3wGR0xvuZcNpMTL-Pp7


Sales 1:=CALCULATE(sum([Total Sales]),filter(‘Table1’,if(HASONEVALUE(Selection1[Version]),Table1[Version]=values(‘Selection1′[Version]),Table1[Version]=”void”)))
Qty 1:=CALCULATE(sum([Qty]),filter(‘Table1’,if(HASONEVALUE(Selection1[Version]),Table1[Version]=values(‘Selection1′[Version]),Table1[Version]=”void”)))
ASP 1a:=DIVIDE([Sales 1],[Qty 1],0)
Price Var a:=([ASP 2a]-[ASP 1a])*[Qty 1]
Mix Var a:=([ASP 2a]-[ASP 1a])*([Qty 2]-[Qty 1])
Vol Var a:=([Qty 2]-[Qty 1])*[ASP 1a]

For all those in selection 2, it the same by driven a different table (selection 2 instead of selection 1)

If you see the numbers in red, they dont have up nicely to the top cat (fruits/Veg)
I want them to be a summation of the numbers below for eg, for Price Var a (-0.4+(-0.4)+0=-0.8 instead of -1.1(which is calculated at the upper row level)

Thanks again!

MattAllington
Posts: 929
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Measure to Sum the Lower Levels.

Postby MattAllington » Thu Feb 01, 2018 7:18 am

The issue is that you can't complete the calculation you are trying to do at the aggregate level. You need to do it line by line.

This should work =sumx(VALUES(Table1[Level 2 Prodcut]),([ASP 2a]-[ASP 1a])*[Qty 1])

To understand why, read my article SUM vs SUMX Explained
Matt Allington is Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/

lalaisgongon
Posts: 3
Joined: Wed Jan 31, 2018 12:36 pm

Re: Measure to Sum the Lower Levels.

Postby lalaisgongon » Wed Feb 21, 2018 5:23 pm

Thanks!!

Works Great!


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests

cron