Total shown incorrectly on the matrix

[Sky]

Hi all,

I have a matrix on the report that shows the YTD amount based on a subgroup. When multiple years are selected from the filter, the total shown on the matrix does not sum up the values for each column across multiple years. It just repeats the last row of the matrix. Could you please advise why and how we can fix it? Thanks.

The value shown for each cell is based on the following metric:

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

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/

[Sky]

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?

[Sky]

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])))+0

Then 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"
	)
)+0

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

[GilbertQ]

Hi @Sky

Thanks for the image but I cannot see exactly what the totals are. Is it possible to extract some of the values and put it into a table in this post so that I could put it into a table on my own Power BI Desktop?

I do think that there is an easier way to achieve this.

[Mmahachi]

@Sky I am having the same issue when I applied the values measure for the post on rob's blog i too got a number much higher than originally calculated. Would love to see a resolution to this. 

on top is ultimately what I need to show a table rolled up at country level with the Backlog value, each country has several 'categories' underneath with their own values for backlog, which you can see in the 'total backlog' column in the bottom table, you can see those values do not add up to $38M but would add up to just under $9M and that is what I want to see at the country level.

The measures:

Total Backlog = [Daily BL Value]*COUNTROWS(BacklogCalendar)

applying the measures from Rob's post it balloned the country value to $3B

Backlog by FY = IF(COUNTROWS(VALUES('APRO Project List'[Project Title]))>1, [Total Backlog], SUMX(VALUES('APRO Project List'[Project Title]), [Total Backlog]))

any suggestions @GilbertQ