Forum Discussion

nh27's avatar
nh27
Helper III
4 years ago
Solved

Calculate discount using two tables

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....
Need Help
Show and Tell
Tips and Tricks
  • VahidDM's avatar
    VahidDM
    4 years ago

    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!!

     

VahidDM's avatar
VahidDM
Super User
4 years ago

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's avatar
nh27
Helper III
4 years ago

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's avatar
    VahidDM
    Super User
    4 years ago

    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!!