Forum Discussion
DAX error calculating Pareto
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.
Super UserI 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.