Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KellyDittmar
Helper II
Helper II

Dax - IF statement comparing two measures

I'm sure there is an obvious solution to this issue - it's awfully hard to google search for help with an if statement! 

 

I work for a consumer goods company - we sell items to customers at MSRP. When we decide to clearance an item, we sell it at X% less than MSRP. My end users desire some reporting to determine if something has been sold at a "close out" level - defined as 65% of MSRP. 

 

My data source is an SSAS tabular cube. We have attributes of our sales - including the customer PO, order date & item(s).

* The sales table level of granulartiy is at a line item level.

* Sales joins to the Order Date Dimension & the Items Dimension.

* A sale has 1 order date with associated columns of Date, Month, Year (and is dense). Each order has 1 and only 1 order date.

* This is all working correctly.

 

I have created two measures - both of which are working great. 

[Shipped Sales $] - the sum of all shipped dollars

[Shipped MSRP] - pseudocode: sum(Item's MSRP * Units Ordered)

 

I then created a new DAX measure:

Consumer Sales Type = IF([Shipped Sales $]<([Shipped MSRP]*.65),"Closeout Sales","Regular Sales")

 

This is where it starts to go poorly... See the attached screenshot.

* I am looking for orders between 3/24/2018 and 4/3/2018. Off-screen - I have another filter to bring in just the one customer PO. 

* Top table is my dataset without my measure. 1 PO ordered on 1 date in one month/year for 1 PCR with a shipped and MSRP of $40.

* The bottom dataset is with my measure - which explodes my result set. Interestingly - Order Date/Month/Year all come from the same record - but it's like the context is lost between them. I still only have the 1 shipped unit for $40, but the duplication indicates an issue which I can't find.

 

2018-04-24_18-36-52.jpg

What I've tried:

* Sum to wrap my measures - made no difference

* adding a second if: IF(not(ISBLANK([Shipped Sales $])) to wrap my original measure - but of course this just hides my bad data (and eventually I run out of memory so this is not a workable solution). 

 

 

 

Any ideas why this is happening? I can certainly create it as a calculated column on my sales table - but I believe there will be cases when they don't want to use 65% as the threshold and I hate to have to change my model constantly... 

11 REPLIES 11

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.