Forum Discussion

BlueRanger's avatar
BlueRanger
Frequent Visitor
1 year ago

DAX Help - complex calculation

Hello,

I am encountering a roadblock in the DAX code I am trying to develop and would appreciate any guidance or support! 

Here is a picture of the work in progress (see below). The first level of the matrix is the promotion description. The next level drills down to the individual items in that promotion. The goal of the report is to compare items sold on promotion against that same item's total sales. There are two measures that we need for the matrix:

Promotion Units: shows how many items sold on promotion

Total Units Sold: needs to show total unit sales for an item. As you can see we have a test measure that shows the total units sold which ignores whether or not a unit sold on promo. As long as >1 of the item sold on promo, we get the total unit sales. The problem is, I am unable to get this to aggregate at the promotion description level. The current measure titled total units sold is incorrect. The correct value should be 1391 for fresh fruit banana and as a total at the description level.

 



The data model is a star schema with transactions being the main fact table. There are three other dimensions being used: date, promotion, and item. Here is a picture of the model:


An item is on promotion if it has a promotion key in the transaction table. If it is null than the item is not included in the promotion. In this example only the top row would be counted as an item sold on promotion.

 

 

I need a way for the measure total units sold to aggregate the total units of any item that falls under a promo description. Is there a way to do this with DAX?

Hopefully that description makes sense. Thanks in advance for any help!

7 Replies

  • some_bih's avatar
    some_bih
    Community Champion

    Hi BlueRanger check example how it is implemented with DAX.DO, Promotion table structure on link. Check also part relatioships with your model (it would be the best to have start and end date of promotion).

    On this link it is example how to calculate sales without discount.

    Hope this help

    • BlueRanger's avatar
      BlueRanger
      Frequent Visitor

      Thank you some_bih (love the name)! I am still running into issues. I do however have promotion start and end date in the promotions table. I provided some sample data below to illustrate what I am trying to accomplish. Start and end date is not included in the sample dataset.

      • some_bih's avatar
        some_bih
        Community Champion

        Hi BlueRanger Honestly, I do not feel well (some flue, I guess). I will not be able to check your case in following days. I am sorry.

  • I think you could create a measure like

    Total Units Sold =
    SUMX (
        KEEPFILTERS ( SUMMARIZE ( Transactions, Item[Item Key] ) ),
        CALCULATE ( COUNTROWS ( Transactions ) )
    )
    

    This assumes that the item in your matrix is coming from the Items table.

  • BlueRanger's avatar
    BlueRanger
    Frequent Visitor

    Thank you for everyone's help. I am still running into the same issue, unfortunately. I'm providing some sample data to illustrate the issue further, this includes a data schema, and desired output. 

    Transactions

    Business DateItem Pack PKPromotion Detail PKUnits Sold
    1/1/2025312
    1/2/20253 558
    1/3/20254 5


    Promotions

    Promotion DescriptionPromotion Detail PK
    Bananas1


    Items

    Item NameItem Pack PK
    Fresh Fruit Banana3
    Nuggies4


    Date

    Date
    1/1/2025
    1/2/2025
    1/3/2025
    1/4/2025
    1/5/2025


    Desired Result (Matrix) in Power BI

     

    Data Model Schema 

     



    • BlueRanger's avatar
      BlueRanger
      Frequent Visitor

      Sorry, the totals for the desired result should actually be: