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...
Need Help
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?

Sky's avatar
Sky
Frequent Visitor
9 years ago

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

    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's avatar
      Mmahachi
      Icon for Advocate I rankAdvocate I
      7 years ago

      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