Forum Discussion

Sky's avatar
Sky
Frequent Visitor
9 years ago

Total shown incorrectly on the matrix

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

 

Need Help

6 Replies

  • GilbertQ's avatar
    GilbertQ
    Icon for Super User rankSuper User
    9 years ago

    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's avatar
      Sky
      Frequent Visitor
      9 years ago

      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's avatar
        GilbertQ
        Icon for Super User rankSuper User
        9 years ago

        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?