Calculate discount using two tables

[nh27]

Hi fellow BI users, I need your help!

I am trying to create a measure called Discount which takes the discount value from a table "Discount_Lookup" and applies it to the row in "ZSALES" table.

There is a Many to One relationship right now from ZSALES to Discount_Lookup.

Discount = SUM(Zsales[Subtotal1])*SUM(Discount_Lookup[Discount])

The formula right now is taking SUM of total discount and applying that, but I want it row by row.

Total sum of discount is 0.82 which is what discount it is applying, but for above I want it row by row only. E.g. row 1 should be 2% of £28,224 and Discount column should be £564.48

Tried a few formula but hit a dead end, any ideas?

Thanks in advance 🙂

[VahidDM · Accepted solution]

HI @nh27 

Can you share a sample of your data (both tables)?

BTW, try this:

Discount = SUM(Zsales[Subtotal1])*MAX(Discount_Lookup[Discount])

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[nh27]

Thank you so much, this seems to have done the trick! I'm confused as to how MAX has resolved this, would you be kind enough to explain why this worked?

Thanks in advance

[VahidDM]

It seems there are many rows for each item in your table and those rows have 2% in the discount column, when you use SUM, it will return the sum of the 2% (if there are 10 rows that will return 20%), but MAX will return just one of them which is 2%.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[YukiK]

Use SUMX() instead of SUM(). Your measure will look like something like this:

Discount = SUMX(Zsales, Zsales[Subtotal1])*RELATED(Discount_Lookup[Discount]))

Please give it a thumbs up if this helps!