Visuals showing incorrect total

[nh27]

Hi all, hoping someone can help.

I have a number of measures which calculate the columns you can see above.

EDG Gross Sales =SUM('Zsales'[Subtotal1]) - SUM('Zsales'[Discount Amount])

EDG Discount = [EDG Gross Sales]*MAX(Discount_Lookup[Discount])

EDG Levy = [EDG Gross Sales]*MAX(Discount_Lookup[Levy_tax])

EDG Net Sales = Zsales[EDG Discount]+Zsales[EDG Levy]

In our Discount Lookup table there will be a number of different % values on rows to calculate discount, in the above example you can see we only have 2% at the moment so the total for EDG Discount is correct 2,779.72.

However for Levy Tax we currently have two rates, 7% and 10%, but the EDG Levy total is calculating wrong, 13,898.62 is 10% of all rows, when in fact one row should be 7%. This is also confirmed by the fact EDG Levy is 10% of the EDG Gross Sales total of 138,986.16.

My question is how do we get the visuals to show the row totals as opposed to taking the max value in the column and applying the max value to every row calculation?

Many thanks in advance.

[v-yalanwu-msft · Accepted solution]

Hi, @nh27 ;

You could try this measure.

EDG Levy2 = 
var _table=SUMMARIZE('Table',[EDG Gross sales],'Table (2)'[Levy_ tax],"1", [EDG Gross sales]*MAX('Table'[Levy_ tax]))
return SUMX(_table,[1])

The final output is shown below:

If this formula is not suitable for your data, can you share more about the structure of your two tables or simple files?
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@nh27 , Ideally you should have moved discount to Zsales and should have done calculation at line level

because we should do Sum(A*B) Not Sum(A) * Max(B)

Now use some visual group bys have measure like

EDG Discount = Sumx(values(Table[Column]), [EDG Gross Sales]*MAX(Discount_Lookup[Discount]) )
EDG Levy = Sumx(values(Table[Column]), [EDG Gross Sales]*MAX(Discount_Lookup[Levy_tax]) )

Also check

https://www.youtube.com/watch?v=ufHOOLdi_jk

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[nh27]

Thanks for the suggestions, creating a SUMX on the Discount and Levy measures seem to have done the trick.

We did consider embedding this into the table itself, however we may have 8-10 different variations for different sales areas on this calculation so we felt using measures would be best suited.

The two below unfortunately did not seem to work:

EDG Discount = Sumx(values(Table[Column]), [EDG Gross Sales]*MAX(Discount_Lookup[Discount]) )
EDG Levy = Sumx(values(Table[Column]), [EDG Gross Sales]*MAX(Discount_Lookup[Levy_tax]) )

Thanks for the links