## 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.

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: 985
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

### Re: Measure to Sum the Lower Levels.

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".
Power BI Training

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

### Re: Measure to Sum the Lower Levels.

Thanks!!

Works Great!

Return to “PowerPivot/DAX”

### Who is online

Users browsing this forum: No registered users and 14 guests