Forum Discussion
Total shown incorrectly on the matrix
Super Userhi Sky
This would be because when the measure is run on the total rows in the table, it is looking for the filter context, of which there is none on the totals row.
So in order to get this working as expected, there is a link below to a great blog post from Power Pivot Pro which explains exactly how to achieve the results that you want to appear in the totals row. I personally use this all the time now to get the totals that I want to appear in the final row.
https://powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/
Thanks guavaq for your kind reply. I checked the link you send me and tried to apply it, but I received the incorrect (way larger than actual) results for each financial year row and also for grand total. So I assume I changed the calculations incorrectly. Sorry I am new to DAX and have difficulty applying the rule discussed in the link to my report. Would it be possible for you to review the calculations and advise where I should apply the change? I don't use CountRows and Values functions to calculate the number of gifts for each month, instead I have used Summarize function.
CountGiftsBrRM calculates the number of transactions we have for each ResponseMethod that appears on columns in the matrix:
CountGiftsbyRM = SUMMARIZE('CashTransactionResponseMethod', "CountGiftsbyRM", CALCULATE(DISTINCTCOUNT(CashTransactionResponseMethod[Transaction_PK])))+0
And YTD amount has been calculated based on the CountGiftsByRM for each ReponseMethod:
Number of Cash Gifts YTD =
IF(
ISFILTERED('CashCalendar'[CalendarDate]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."),
TOTALYTD(
sum('CashTransactionResponseMethod'[CountGiftsbyRM]),
'CashCalendar'[CalendarDate],all(CashCalendar),"06/30"
)
)+0
- GilbertQ
Hi Sky
Do you perhaps have some sample data I could have a look at?
I am thinking that it might be possible to create a measure instead of using the SUMMARIZE function, which would make everything easier with measures, as well as then get the expected output for your matrix.
Also what would be the expected total for your matrix?
Hi guavaq, thanks for looking into this! Please find below a snap shot of first few records:
CashTransactionResponseMethod
The YTD is the total number of transactions for each Response Method (shown on the column header). I am counting the number of transactions based on CashTransactionResponseMethod[Transaction_PK]
To be able to calculate the YTD for "Each Reponse Method", I have created a calculated column called CountGiftsbyRM using Summarized function, not sure if there is any better way to do that. I would appreciate it if you could point me to a better way.
CountGiftsbyRM = SUMMARIZE('CashTransactionResponseMethod', "CountGiftsbyRM", CALCULATE(DISTINCTCOUNT(CashTransactionResponseMethod[Transaction_PK])))+0Then Number of Cash Gifts YTD calculates the total number for each Response Method based on the CountGiftsByRM column calculated above for each FY and Month selected in filters:
Number of Cash Gifts YTD = IF( ISFILTERED('CashCalendar'[CalendarDate]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy."), TOTALYTD( sum('CashTransactionResponseMethod'[CountGiftsbyRM]), 'CashCalendar'[CalendarDate],all(CashCalendar),"06/30" ) )+0The Total should show the sum of values shown in each column (accross selected Financial Years), rather than repeating the values from the last financial year on the matrix.
I much appreciate your help.