Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
NorbertManuel
Frequent Visitor

Running total with incorrect subtotal column in a matrix table

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).

 

NorbertManuel_0-1682526602119.png

And here is my dax measure

NorbertManuel_1-1682526710931.png

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

 

Many thanks for your help

4 REPLIES 4
NorbertManuel
Frequent Visitor

@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

 

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

@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

Greg_Deckler
Community Champion
Community Champion

@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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.