Power BI running total measure not working when no data for dimension

Question

I tried to do a running total but I am struggling.

The problem is when there is no data for the dimension.

In the screenshot below, for 'Véhicule soumis', there is no data (i.e. Total = 0). In this case, the 'Running Total' returned is the grand total of 35 instead of 14.

The measures are:

Total = IF(ISBLANK(COUNTROWS(FactCreditApplications)); 0; COUNTROWS(FactCreditApplications))
Running Total = CALCULATE([Total]; FILTER(ALLSELECTED(FactCreditApplications); [StatusId] >= MAX([StatusId])))

What I am doing wrong?

Thank you

Answer 1

The Running Total measure is using the StatusId from your fact table rather than your dimension table. Since the StatusId for 'Véhicule soumis' doesn't exist in your fact table, it behaves unexpectedly.

I'm assuming you have the StatusId column in your dimension table. If you do, it should simply be a matter of switching your measure to use the column in that table instead. E.g.:

Running Total = CALCULATE([Total]; FILTER(ALLSELECTED(DimStatus); [StatusId] >= MAX([StatusId])))

assuming your dimension is DimStatus