topic DAX Help - complex calculation in DAX Commands and Tips

DAX Help - complex calculation

BlueRanger — Mon, 13 Jan 2025 23:46:05 GMT

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!

Re: DAX Help - complex calculation

some_bih — Tue, 14 Jan 2025 06:29:06 GMT

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

Re: DAX Help - complex calculation

johnt75 — Tue, 14 Jan 2025 13:54:24 GMT

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.

Re: DAX Help - complex calculation

BlueRanger — Tue, 14 Jan 2025 17:31:04 GMT

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 Date	Item Pack PK	Promotion Detail PK	Units Sold
1/1/2025	3	1	2
1/2/2025	3		558
1/3/2025	4		5

Promotions

Promotion Description	Promotion Detail PK
Bananas	1

Items

Item Name	Item Pack PK
Fresh Fruit Banana	3
Nuggies	4

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

Re: DAX Help - complex calculation

BlueRanger — Tue, 14 Jan 2025 17:32:51 GMT

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.

Re: DAX Help - complex calculation

some_bih — Wed, 15 Jan 2025 07:39:38 GMT

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.

Re: DAX Help - complex calculation

BlueRanger — Wed, 15 Jan 2025 16:22:20 GMT

No worries - I appreciate your time. I hope you feel better!

Re: DAX Help - complex calculation

BlueRanger — Thu, 16 Jan 2025 16:45:21 GMT

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