lalaisgongon
New Member
- Joined
- Jan 30, 2018
- Messages
- 1
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 http://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!
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 http://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!