Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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...
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |