Newbie Question to SUMX()

[janosh]

Hi everyone.

Data:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

I assume a Unitprice of $10, therefore I can calculate the Total Turnover as follows:
TotalTurnover:=SUMX(factSales;factSales[Qty]*10) ==> 70

Furthermore, I would like to calculate a 10% discount and I compare the following two options:

TotalNetTurnover1:=SUMX(factSales;[TotalTurnover]*0.9) ==> 81
TotalNetTurnover2:=[TotalTurnover]*0.9 ==> 63

I actually expected both options should result in the same result. But the first option seems to be wrong (81).
The difference between above two formulas is - I guess - due to having customer A ordered one piece twice (2 identical rows). Is this a bug in powerpivot or is it logical to you? May you help to explain?

Many thanks and best regards,
janosh

 

[Ozeroth]

Hi janosh,

The reason TotalNetTurnover1 returns an unexpected result is that it effectively performing one SUMX inside another, both iterating over the same table, with the inner SUMX being wrapped in an implied CALCULATE() (because it's within the [TotalTurnover] measure). This results in double-counting of some rows in this particular example.

I would avoid nested SUMX over the same table in this sort of situation and use something like TotalNetTurnover2.

To explain in more detail:

[B]TotalNetTurnover1[/B]:=SUMX(factSales;[B][COLOR=#ff0000][TotalTurnover][/COLOR][/B]*0.9)

is identical to

TotalNetTurnover1 :=
SUMX ( factSales; [B][COLOR=#ff0000]CALCULATE ( SUMX ( factSales; factSales[Qty] * 10 ) )[/COLOR][/B] * 0.9 )

The implied CALCULATE inside the outer SUMX turns the row context into an equivalent filter context (context transition), and then the inner SUMX sums over factsales filtered by that context.
(This is a good article on context transition: https://www.sqlbi.com/articles/understanding-context-transition/)

In the case of TotalNetTurnover1, the steps look something like this:

Row 1
Row context = (Customer = A & Quantity = 1) =>
Sum [Qty]*10 and multiply by 0.9, over rows of factSales where (Customer = A & Quantity = 1) = (1*10 + 1*10) * 0.9 = 18

Row 2 (same as Row 1)
Row context = (Customer = A & Quantity = 1) =>
Sum [Qty]*10 and multiply by 0.9, over rows of factSales where (Customer = A & Quantity = 1) = (1*10 + 1*10) * 0.9 = 18
(Since Row 2 = Row 1, Rows 1 & 2 end up being double-counted, not the behaviour you want!)

Row 3
Row context = (Customer = B & Quantity = 2) =>
Sum [Qty]*10 and multiply by 0.9, over rows of factSales where (Customer = B & Quantity = 2) = (2*10) * 0.9 = 18

Row 4
Row context = (Customer = B & Quantity = 3) =>
Sum [Qty]*10 and multiply by 0.9, over rows of factSales where (Customer = B & Quantity = 3) = (3*10) * 0.9 = 27

Total of above = 18 + 18 + 18 + 27 = 81

 

[janosh]

Thanks a lot for your explanations.

Let's say the deduction factor is not fixed 0.9, but it's defined within the data (and different for each row), so I cannot use TotalNetTurnover2, and I'm back at SUMX():
TotalNetTurnover:=SUMX(factSales; [TotalTurnover]*factSales[deductionFactor]) ==> I guess this will bring back to issue with double count

Instead of [TotalTurnover] I could enter the full formula definition of TotalTurnover, but this gets very confusing after nesting some mesaures, right?
Alternatively, I could create calculated columns, but in this case the file size will be blown up.

So is there any "best practice" tip from your side for such a situation?

rgds, janosh.

 

[Ozeroth]

This is the simplest way of doing it I can think of. Replace [TotalTurnover] with the row-level formula (without the SUMX):

TotalNetTurnover:=SUMX(factSales; factSales[Qty]*10*factSales[deductionFactor])