Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi I have a issue with Matrix table sub total,
I have craeted below measures
Solved! Go to Solution.
@cham Try:
Measure Total =
VAR __Total = SUMX(SUMMARIZE('Table',[End of Month],"__PrevMonth",[PreviousMonthMeasure]),[__PrevMonth])
RETURN
IF(HASONEVALUE('Table'[End of Month]),[PreviousMonthMeasure],__Total)
@cham You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
In your case, maybe:
Amount Sales = SUM(Sales[Amount])
Prev Month To Date =
VAR __Date = MAX('Calendar'[Date])
VAR __PreviousMonth = DATE(YEAR(__Date),MONTH(__Date)-1,DAY(__Date))
VAR __FirstOfPrevMonth = DATE(YEAR(__PreviousMonth),MONTH(__PreviousMont),1)
RETURN
IF([MTH]=1, 0, CALCULATE ([Amount Sales], Calender[Date]>=__FirstOfPrevMonth, Calendar[Date]<=__PreviousMonth))
Sales Amount Mth = [Amount Sales] - [Prev Month To Date]
The amount which are showing for each month is corrcet. The issue I am having is total for each year.
Ex: 2019 Total value is not the SUM of the 2019 Months Value.
Can you please help for this issue?
Thanks
@cham Oh, I misread. 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
Please see the screesnhot below,
2020 year total is giving e the corrcet sum of 2020 months
But the measures I used to get the previous month value is correct. Every month is giving the previous month value but the Total of all the months values showing in sub total is incorrect.
WhatI can do for that.
thanks.
@cham See MM3TR&R it's essentially the same issue as with measure totals: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community
Hi,
I am confused but I'd also like to help you. Please show clearly in an MS Excel file, the result you are expecting.
Please find the below details,
This is the Power BI table - which is not givingthe current SUB total of 2019 months
This is the excel data set, Power BI sub total needs to be as below highlited total.
Thanks,
Hi,
Share the link from where i can download your PBI file.
@cham Try:
Measure Total =
VAR __Total = SUMX(SUMMARIZE('Table',[End of Month],"__PrevMonth",[PreviousMonthMeasure]),[__PrevMonth])
RETURN
IF(HASONEVALUE('Table'[End of Month]),[PreviousMonthMeasure],__Total)
hi @Greg_Deckler ,
Its not giving the correct totals what I want. Thank measure change my month totals as well.
@cham Can you post sample data as text so that we can replicate the issue?
Hi Grey Your measure worked on this. Thank you so much for your help.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |