DAX error calculating Pareto

[Juanes_Agudelo]

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!

[johnt75 · Accepted solution]

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.

[Juanes_Agudelo]

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.  

[Juanes_Agudelo]

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.