Measure to Sum the Lower Levels.

Anything related to PowerPivot and DAX Formuale
lalaisgongon
Posts: 1
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: 919
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 Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 4 guests

cron