Forum Discussion

dresinger's avatar
dresinger
Frequent Visitor
3 years ago

Applying Discounts to a Sales Table

I've done quite a bit of searching and cannot find how to properly calculate this, any help is appreciated!!!

 

I have 2 tables, 1 for sales, broken down by customer/product/price, and another that tracks customer discount data by customer/product/discount (either dollar amount discounted, % discounted, or both).  Tables are linked by the "Customer" column.  Examples of the tables are shown below.

I need to apply the discounts to each row on a per customer/per product level.  Depending on the negotiated discount sometimes these will be a dollar amount, sometimes they will be a % amount.  I have accomplished this using a measure, however I am running into issues when the negotiated discount applies to "ALL Products".  I cannot figure out how to apply a % discount to "All Products for that particular customer...

 

The current measure (that doesn't work with "All Products" discounts is below.

 

Current Discount Price =
If(AVERAGE('Discount'[Discount $])>0,
AVERAGE('Sales'[Price] - AVERAGE('Discount[Discount $]),
IF(AVERAGE('Discount'[Discount %]>0,
AVERAGE('Sales'[Price])*(AVERAGE('Discount'[Discount %])*.01)),
AVERAGE('Sales'[Price]))))

 

How can I adjust this measure to accommodate the "All Product" % Discount?

 

Chanigng/merging the datasets is not possible because of additional columns and calculations I have in my dashboard.

Need Help

1 Reply

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    3 years ago

    Hey How about,

    First of all I recommend that you create an index column in the table that stores the discounts and create the same column on the sales side, in this way you will have a 1 * discount in the direction of the sales table, this will allow you to access (either with a calculated column or with a measure) to the different customer discounts.