Applying Customer Discount to Sales Measure

DAX DAX Calculations
1

Hi All,

I am struggling with an issue regarding applying a Customer Discount to a Sales Measure.

I have a Line Level FactSales Table that is Related to a Customer Table via the CustomerID.

I have a Sales Measure Called Net Sales = SUM(FactSales[Amount] - SUM(FactCredit[Amount])

I want to apply a Discount which is held in the Customer Table for each Customer to the Net Sales Measure but cannot figure out how I apply it to an already aggregated measure. If it was Line Level I would simply use the Related function to grab the discount but that wouldn’t work in this scenario.

Any help appreciated.

Thanks

Chris

2

Chris,

More than likely going to use a measure with CALCULATE and FILTER the discount. Please provide a sample PBIX file so we can better assist. Also please read all of this material before posting questions on the forum.

https://forum.enterprisedna.co/t/does-your-support-question-have-everything-required-to-be-answered-effectively/3953

Thanks
Jarrett

3

Hi Jarrett,

Please find attached a copy of the PBIX File and a picture of the model.

The requirement is to Multiply the Net Sales Measure by the NPCusRebate % in the Customer Table to find how much Rebate that we have given by Customer, Location (Each Customer Belongs to a Location) and Date etc. In my model here I have a Date Table. Hope this makes sense.

Thanks

Chris

EDNA Model
EDNA Model1048Ă—632 15.4 KB

Sales EDNA Upload.pbix (85.2 KB)

4

Chris,

The issue we are running into is the way your model is currently setup. 1st I would merge the credits and sales table into 1 Like this:

image

Then the other issue is that none of your CustomerID’s in your Customer Table match any of the CustomerID’s in either of your Credits or Sales Tables?

image
image

We can not create a measure for rebate unless we get Customer ID’s to match, unless you come up with some other way to uniquely identify each of your customers.

Thanks
Jarrett

5

Hi Jarrett,

Setting up the Credits in the Sales Table isn’t an option, they have to be seperate for technical reasons of how the erp is setup.

The CustomerID’s are missing because I have butchered the Report I have here due to the upload limit of 4096 so I will put that right and repost.

Sales EDNA Upload.pbix (85.2 KB)

6

Chris,

None of the ID’s still match?

image
image714Ă—415 14.2 KB

Here is an example of Data that I put together with something similar to your data. In my example all of the ID’s from the Rebates Table Match the Sales & Credits Table ID’s. This would work similar even if tables were separated. You can use the same techniques I used to get the values you want for your model.

Sales EDNA Upload- Jarrett Example.pbix (109.0 KB)

Thanks
Jarrett

7

Hi Jarrett

Sorry, I uploaded the wrong version. I have attached the proper version, it has been one of those days.

I don’t think you method will work because I think I would have to create a BI Directional between FactCredit and Customer which is likely to Create ambiguity.

Thanks for all your help.

Chris

Sales EDNA Upload.pbix (85.2 KB)

8

Chris,

You finally got one ID to match in your fact tables, but that ID would also have to be in the Customer table so that we can apply a rebate.

image
image726Ă—410 17.5 KB

Thanks
Jarrett

9

I have no idea what went on there but this is the real example.

You example will work if the Customerid is on the grid but as soon as you drop the locationid in it doesnt work. I would guess it is to do with the Row Context.

Thanks

Chris

Sales EDNA Upload.pbix (91.3 KB)

10

Chris,

Location is not going to work if you have CustomerID’s from the same location that have a different Rebate %. If they all have the same Rebate%, then all you need to do is just add location to the Customer Table. I’ll have a look into you file tomorrow morning.

Thanks
Jarrett

11

Chris,

Here is my solution the way your current model is setup. I took the logic from Sam’s material on Parameter Tables at the following link. Please review so you understand the logic.

Sales EDNA Solution 4-21-2020.pbix (93.2 KB)

Thanks
Jarrett

12

Hi Jarrett,

Thanks for your efforts, I actually ended up with the same solution.

I know you think there is an issue with the model but there really isn’t.

Have a good day.

1 Like