topic Running total with incorrect subtotal column in a matrix table in DAX Commands and Tips

Running total with incorrect subtotal column in a matrix table

NorbertManuel — Wed, 26 Apr 2023 16:33:24 GMT

Dear community,

I cannot fix my issue below, even after having spent hours to sort that leveraging existing resources.

Here is my problem.

I have a measure, which is a running total, shown in a timeline matrix. This measure works well and total of each column is ok, but column subtotal is not: I've got the last column value, not the sum of each of the previous ones. To be clear, I have set this measure to show recurring revenue from contracts which are invoiced on a monthly manner from a start date. I need the total revenue by month (column total) and for the entire period (a quarter, a year, etc. which is my column subtotal).

And here is my dax measure

And of course I need that dynamically updating with the timeline selection.

Many thanks for your help

Re: Running total with incorrect subtotal column in a matrix table

Greg_Deckler — Wed, 26 Apr 2023 16:46:59 GMT

NorbertManuel First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

Also: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community

Re: Running total with incorrect subtotal column in a matrix table

NorbertManuel — Wed, 26 Apr 2023 17:10:34 GMT

Greg_Deckler thanks, unfortunately I have already went through these materials without being able to leverage them, as my issue is not at row level, but column level (as the total in each column is ok).

thanks

Re: Running total with incorrect subtotal column in a matrix table

Greg_Deckler — Wed, 26 Apr 2023 21:05:49 GMT

NorbertManuel Can you clarify for me. The total that is wrong is the subtotal of a row (first level of what you have in your Rows field well correct? If that is the case then a variation of MM3TR&R should do the trick. If you could post sample data that would be ideal but perhaps something like this:

Measure = VAR __Measure = [Measure] // this is the measure in question VAR __Result = IF( ISINSCOPE( [Column2]), // this is the second level of your hierarchy, could also possibly use HASONEVALUE() __Measure, // if 2nd level is in scope/has one value then just return the measure VAR __Table = SUMMARIZE('Table', [Column2], "__Measure", [Measure]) VAR __Result = SUMX(__Table, [__Measure]) RETURN __Result ) RETURN __Result or alternately: Measure = VAR __Measure = [Measure] // this is the measure in question VAR __Result = IF( ISINSCOPE( [Column2]), // this is the second level of your hierarchy, could also possibly use HASONEVALUE() __Measure, // if 2nd level is in scope/has one value then just return the measure VAR __Table = ADDCOLUMNS(DISTINCT('Table'[Column2]), "__Measure", [Measure]) VAR __Result = SUMX(__Table, [__Measure]) RETURN __Result ) RETURN __Result

Re: Running total with incorrect subtotal column in a matrix table

NorbertManuel — Thu, 27 Apr 2023 09:25:39 GMT

Greg_Deckler thanks for spending time on my case.

No, the issue is not that one.

Let me clarify with the picture of my matrix:

If you look at Feb'23 column, total of the column show 6.28 which is correct (2.18+0.55+0.19+3.26+0.1)

And same for the next columns.

The issue is with the subtotal column (the last one in bold called "total"): as you can see, for the first row for instance, this is showing 5.81, which is basically the same value as Dec'23. And same for following rows. This is not surprising because my measure is a kind of rolling YTD (meaning not stopping at the end of each year, but a running total).

However, my objective is to get the value of the total recurring revenue invoiced each month, for the selected period (here the whole year 2023). So, instead of showing the "exit rate", ie the value invoiced in Dec'23, I want to get the value from Jan'23+Feb'23+...+Dec'23. And that should be dynamic, ie if I select Q1'23 in my timeline, I will need to see Jan, Feb, Mar and then get the sum of these 3 months.

Hope this helps.

Cheers