Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
@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
@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
@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
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
12 | |
10 | |
10 | |
6 |