Forum Discussion
DAX error calculating Pareto
Hi everyone,
I'm trying to calculate which customers are responsible for 80% of the YTD revenue in my model, based on the month selected by the user via the 'Calendar'[Month Year] slicer (e.g., Mar 2025).
The [Selected_Revenue YTD] measure works as expected — it correctly calculates revenue from January 1st up to the selected month.
The issue is when a customer has no records in the selected month, even though they have YTD revenue from earlier months. In those cases, my Pareto cumulative measure (Pareto Line YTD) incorrectly returns 100% (Image 2 below), and the customer seems to be missing from the visual.
For example, in the case of "ROTHESAY LIFE PLC" in February 2025, the measure shows 100% even though they had revenue in previous months. To debug this, I added conditional formatting (red background) to the table — and as seen in the attached screenshot, the cell is not highlighted, indicating that the customer row is not properly evaluated.
Image1:
Image 2
Image 3
I've tried multiple variations of the Pareto measure, but the issue persists.
Measure until selected month YTD:
Cumulative pareto percentage (Some AI help. Pretty code but not the expected result):
The model is pretty simple — just two tables, as shown in image below.
Any help or suggestions would be greatly appreciated!
Thanks in advance!
I think that the problem arises because you are using the customer data from the fact table, not from the customer dimension table. When you are using SUMMARIZE( ALLSELECTED( 'Fact_Revenue'[Customer] ) ) that will only retrieve customers who have a row in the fact table for the selected month. You could instead use SUMMARIZE( ALL( 'Fact_Revenue'[Customer] ) ) or use a column from the customer dimension table instead.
You could also simplify your Selected Revenue YTD measure to
Selected Revenue YTD = CALCULATE ( [Revenue], DATESYTD ( 'Calendar'[Date] ) )That won't fix the problem, but it is easier to read and might give a small performance boost.
3 Replies
johnt75Super User
I think that the problem arises because you are using the customer data from the fact table, not from the customer dimension table. When you are using SUMMARIZE( ALLSELECTED( 'Fact_Revenue'[Customer] ) ) that will only retrieve customers who have a row in the fact table for the selected month. You could instead use SUMMARIZE( ALL( 'Fact_Revenue'[Customer] ) ) or use a column from the customer dimension table instead.
You could also simplify your Selected Revenue YTD measure to
Selected Revenue YTD = CALCULATE ( [Revenue], DATESYTD ( 'Calendar'[Date] ) )That won't fix the problem, but it is easier to read and might give a small performance boost.
I dont have a Customer dimension table. but your statement "that will only retrieve customers who have a row in the fact table for the selected month" makes me think that your approach is correct. I will create a calculated Customer table and I will share results.
You were right on "that will only retrieve customers who have a row in the fact table for the selected month".
For people with similar issue, the solution to work with a Dimention table AND remenber to Add to the visualisation the customer field from the dimentional table. Otherwise, it will not work.
