I am having a bit of an issue with a DAX calculation and need some guidance. Measures along with a table have been created to pareto sales into two buckets: Above 0 or Below 0.
The problem, which I can’t seems to solve, is that the below 0 total is incorrect. The value shown below is -1,243 but this should be -1,271.
From what I gather, the issue stems from a record with no sales in the current period but has sales in the preceding period. The information in the sales table is correct but when put into a graph the sales delta for Integrated Circuit goes missing.
@MichaelRWechselberge, I agree that what you’re seeing is related to Integrated Circuit’s CY sales. But I disagree with your assertion that “The value shown below is -1,243 but this should be -1,271”.
Imagine you have an empty basket. Someone asks you to take out 28 apples from the basket. Since you don’t have any apples in the basket to begin with, you still have an empty basket after the request. You don’t have a basket with “-28 apples”, you just have an empty basket. Similarly, subtracting last year’s 28 units from this year’s nothing leaves you with nothing rather than -28.
Your CY-PY Total Unit Delta measure incorrectly returns -28 for Integrated Circuit when it should return blank. All your negatives sum to -1243 rather than -1271.
If you drop your [Sales Customer Decrease Qty] into that table you’ll see that it, also, totals -1243.
@HufferD, thank you for the feedback and I appreciate your comments. Topics like this albeit frustrating are positive as it forces us to think about the situation and its perspective.
I feel that the CY-PY Delta measure is correct as it compares current sales volume to the previous period. In the example of Integrated Circuit, they purchased 28 pieces last year with no purchases in the current year. From a sales perspective this is good information and tells me when added up that I need to sell 777 pieces more to break-even to the sales of the previous period.
Your point is valid in that if you had no sales then you can’t have a loss. In this case, if only looking at current sales, then the -1,243 and plus 494 is correct. What I am hung up on is the fact that the CY-PY Delta is the underlying measure used to put values into either the above or below bucket. Somehow those 28 pieces are disappearing between measures and are not being assigned to the below zero bucket?
The reality is that we lost sales of 1271 pieces but only gained 494 pieces for a difference of 777 pieces with one customer having no current sales. This is why I think that the below zero bucket should be -1,271 and not -1,243. The graph delta and table delta both should reflect -777 pieces.
I’m coming from the financial background. I would say that you need to include the decrease in sales/units from the current year vs prior year. If you don’t have any sales for the current year but have from previous year. Why did it decrease in the line of business?
@MichaelRWechselberge + @Keith, I get it. Just about any field, mine included, treats concepts like this similarly.
My point is that you’re not operationalizing the concepts correctly: you’re saying, “when there are no current year units sold for a party, treat that as having zero sales for the year,” but your measures are saying, “when the current year unit sales are missing, ignore them in aggregations”.
This is partly a modeling issue. You should add a dimension table for your Sold-to-party-name. Because Intergrated Circuit has no sales for the current year, it isn’t included in your measure when you filter the table to those having sales < 0. It just isn’t there because your fact table has no values for Intergrated Circuit for the current year. Not “zero” values. No records at all. That is why you don’t see their -28 contributing to the sum of negative sales.
Create a standard dimension table for your ‘Sold-to-party-name’ dimension, ensuring there’s one row for each unique name regardless of whether they have or don’t have sales for a given year. and, rather than using the Sold-to-party-name column from your fact table instead use the one from the (shorter) dimension table. not only is the scanning faster but also when a Sold-to-party-name doesn’t have sales for a given period, like Intergrated Circuit for the current year, they are still included in the table.
Dive into the challenge of DAX Grouping Issue! Unlock the solution to streamline data organization and unleash the full potential of your analytics. It’s time to conquer complexity and elevate your insights!
