Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I created a Running Total Quick Measure by Month, which I am filtering to only the last 6 months. When filtered, the totals begin with the total from the first month displayed, and not from the beginning. I would like for the total on the first month start with the total from the beginning of time, and not from that month. Is this possible? Below is the formula from the Quick Measure and a screen shot of the table.
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @ScottR-MT ,
Please create an extra measure, add it to Matrix.
New Measure = SUMX ( ActualTableName, [IB] )
Regards,
Yuliana Gu
Hi,
Thank you, but this did not work.
Let me clarify. The running total formula works as it is supposed to when all of the data is unfiltered in the visual. I only want to show the last 3 months, but when I do that, the running total starts at the earliest month shown in the visual after filtering. I want the total of the earliest month to be the total from all months from the beginning. If it works as I would like it to in the bottom table, Dec-18 should be 80, and then grow from there. I have attached an example PBI file, and added the below screenshot to show the difference.
The top table is unfiltered, and the running total is correct. In the bottom table, I filtered by the last 3 months, and as you can see, it does not give me the actual total from the beginning of time, but rather the total starts from the first month in the visual.
Any help would be greatly appreciated!
Download: PBI Running Total Example
Quick Measure:
Count of Serial Number running total in Install Month = CALCULATE( DISTINCTCOUNT('Data'[Serial Number]), FILTER( CALCULATETABLE( SUMMARIZE('Data', 'Data'[Month No], 'Data'[Install Month]), ALLSELECTED('Data') ), ISONORAFTER( 'Data'[Month No], MAX('Data'[Month No]), DESC, 'Data'[Install Month], MAX('Data'[Install Month]), DESC ) ) )
Hi,
You may download my PBI file from here.
Hope this helps.
I need to bring this back up again. While the formula provided worked well before, I am now having trouble with it again. This time, the visual will not display the (Running) Total IB amount from the previous months when there is no value for the particular row. It may be because I am using it with SUM instead of COUNT or DISTINCTCOUNT, but I don't know for sure.
Here is my formula, and below is my screenshot of the result:
# IB = CALCULATE(SUM('Install Base'[Model Count]), DATESBETWEEN('Install Base'[Inst_Date], MINX(ALL('Install Base'[Inst_Date]), 'Install Base'[Inst_Date]), MAX('Install Base'[Inst_Date]) ) )
Hi,
To your visual, drag Year and Month from the Calendar Table. In your formula, replace 'Install Base'[Inst_Date] with Calendar[Date]
Yes it did, thank you very much!
Now for the follow-up...
How can I show the regular totals for each line item, but with running totals for only the total line?
Hi,
I don't think that is possible - "running totals for only the total line".
Hi,
Share some data and show the expected result.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |