The basket sales of orders contain certain items

DAX DAX Calculations
1

Hi, I have one promotion product table, and one Fact table. I need to calculate the Basket sales of the orders that contain the promotion products (not the product sales). For example, if one order contains promotion product A (sales of $20) and non-promotion product ($50), the total basket sales for this order is $70.

Could you please help me to understand how to calculate the basket sales of orders that contain promotion products?

Thanks!

MikeOrder_Total.pbix (236.7 KB)

2

Hi @Mike,

I’m unsure what you are expecting here AND I have some concerns about your model.

image

  1. You have a SKU Dimension table which doesn’t include all SKU’s present in Sales
  2. You don’t have a “Promotion product table” in the model
  3. Even though you aren’t performing Time Intelligence calculations at this time, your Sales table includes a Date field - so as a best practice you need to include a Date Dimension table as well and mark that as a Date table

So review your question and provide additional details, preferably with a mock up of the expected result AND an updated model.

Thanks!

3

@Melissa thanks. I have updated the file. The SKU table actually should be the promotion SKU table. I also added Date table. The Fact table might not have all the SKU in Promotion SKU table because one Promotion SKU might not have sales. On the Promo SKU table, I also added Promo Start Date and Promo End Date. Is it possible to find out the Basket sales of orders that contain promo SKU during the campaign period? For example, SKU 494991 was on promotion from May 1 to May 10, the order 11991918 contains this SKU and I’d like to get the total basket sales of 4751.44.

Thanks!

Mike

Order_Total.pbix (265.7 KB)

image
image821×287 10.8 KB

4

Hi @Mike,

Thanks!
So I marked the Date table as a Date table, added a SKU dimension table and updated the relationships.
Next created this measure:

Total Basket Sales = 
    CALCULATE( [Sales],
        REMOVEFILTERS( SKU[SKU] ),
        VALUES( Sales[Order Number ID] ),
        FILTER( VALUES(Dates[Date]), 
            Dates[Date] >= MIN( Promo_SKU[Promo_start] ) &&
            Dates[Date] <= MAX( Promo_SKU[Promo End Date] )
        )
    )

.

With this result.
image

Here’s your sample file. Order_Total (2).pbix (210.9 KB)
I hope this is helpful.

5

@Melissa thanks for your time and help, that is awesome. I checked your file and saw one column called 'VisibleSKU", could you please help me understand the purpose of this column?

Thanks!

Mike

6

Hi @Mike,

It’s not a column but a Measure that I used on the SKU slicer (check out the filter pane) it filters down to only SKU’s that are present in the Promo SKU table.

7

@Melissa sorry, my bad. yes it is a measure, but I don’t see you used the measure as a slice. so just waiting if I need to create the measure.

Thanks!

Mike

8

Hi @Mike,

It’s used as a filter on the SKU slicer visual, so you need to select it first
image

9

@Melissa Sorry, I missed that. Could you please explain to me why we need this measure as a filter for this visual? Thanks!

Mike

10

Need is a big word but remember I changed your model so the SKU dimension table now includes all SKU’s from both Promo and Sales. In your original file you had the Promo SKU field in the Slicer so excluding all SKU’s only present in Sales.
Now the SKU dimension field is placed in the Slicer so including also all SKU’s present in Sales and the measure ensures that you can only see and select an item if that’s present in the Promo SKU table, like you had previously but sure you can remove it if you don’t want/need it.

11

@Melissa that is awesome. I will replica your solution into my real dataset. Thank you very much for your help!

Mike

12

@Melissa hi, Melissa, just have one quick question for you. What if in my promotion SKU have duplicate SKU, for example, one SKU could have multiple promotion during a year. Do you know if this method will still work? Thanks in advance!

Mike

13

@Melissa, Hi , Melissa, I did the exact what you did in your data model, but I have the following error message for the Basket Sales.

image

Not sure what caused the problem. I have triple checked my data type and model. That is weird.

Mike

14

Hi @Mike,

Can you provide a new sample PBIX, so I can review the model as is and incl. duplicates?
There is another issue with the measure I provided so I’ll address that as well then…

Thanks!

15

@Melissa Hi, Melissa, thank you very much. I will upload the file shortly.

Mike

16

@Melissa, Hi, Melissa, I have updated the promotion SKU list and Sales table, please see the attachment. Thank you for your help! Really appreciate it!

MikeOrder_Total (2).pbix (211.0 KB)

17

Hi @Mike,

See how this works for you.

image
image707×265 9.58 KB

Here’s your sample: Order_Total (2).pbix (211.5 KB)
I hope this is helpful.

18

Hi @Melissa, thanks a lot. The data in the image looks right to me. I will download your file and try to understand how that works.

Thanks again!

Mike

19

@Melissa HI, Melissa, I tested it and it is working as I expected. That is awesome. Thank you very much. One question, with this data model, do you know if it is possible to calculate the sales of promotion SKU prior to campaign start date? for example, if a campaign starts at May 20, 2020 (campaign start date and end date will be in the promotion SKU table), when select this campaign on the visual, is it possible to have a measure to show the sales of the campaign SKU 4 weeks prior to the campaign?

Thanks!

Mike

20

Hi @Mike,

For that you’ll need to look into Time Intelligence calculations, you can try something like:

Total Basket Sales show 4 weeks prior to campaign = 
    CALCULATE( [Total Basket Sales],
        DATEADD( Dates[Date], 4*7, DAY )
    )