cancel
Showing results for
Did you mean:
Highlighted
Member

## Matrix Totals Not Matching

I am familiar with the matrix measure triple threat, love that thing. I am using that in a way to get some totals.

I have 3 measures, CMI and Customer Count To Date. CMI is cumulative / additive, and Cust Count is semi-additive and changes each month. The third measure is SAIDI which is CMI/CustomerCount and is what I'm really interested in. The others are plotting easily with the measures and appear accurate. SAIDI though, with a straight total down the column when visualized monthly, the measure 'total' at the bottom doesn't equal what you would get if you added them by hand because of the semi-additive measure. I'm attempting to use one of the patterns I've seen posted to handle this, so that if more than one month is present it uses a summarize. What's really strange to me though is that, depending on where I put the 'all()' filter, I get or do not get the correct answer in my visual. My gut tells me it is related to the order of operations either within the calculate/calculate table or within the execution of the VAR part or something along those lines.

Here is the offending measure SAID that works correctly:

`SAIDI = //Var __Dt = max('2 UDM DateTable'[CALENDAR_DATE])var __Yr = maxx(ALLSELECTED('2 UDM DateTable'),[CAL_YEAR_NBR])//var __Mon = month(__Dt)var __tmpTable = CALCULATETABLE(    Summarize(        '2 UDM DateTable'        ,'2 UDM DateTable'[CAL_YEAR_NBR]        ,'2 UDM DateTable'[CAL_MONTH_NBR]        ,"Cust Agg",[Customer Count To Date]        ,"CMI Agg",[CMI]        ,"SAIDI Agg",[SAIDI Form]    )    ,filter(all('2 UDM DateTable'),'2 UDM DateTable'[CAL_YEAR_NBR]=__Yr))VAR __SubTotal =  sumX(__tmpTable,[SAIDI Agg])returnif(HASONEVALUE('2 UDM DateTable'[CAL_MONTH_NBR]) && HASONEVALUE('2 UDM DateTable'[CAL_YEAR_NBR])    ,[SAIDI Form]    ,if(HASONEVALUE('2 UDM DateTable'[CAL_YEAR_NBR])        ,sumX(__tmpTable,[SAIDI Agg])        ,blank()    ))`

This produces the following table:

`Year	SAIDI2019	73.821	15.092	3.993	7.174	5.355	8.556	12.167	10.268	8.129	3.12Total	73.82`

This version works for the 'total' at the bottom of the matrix, but does not work for the '2019' subtotal row:

`SAIDI = //Var __Dt = max('2 UDM DateTable'[CALENDAR_DATE])var __Yr = maxx(ALLSELECTED('2 UDM DateTable'),[CAL_YEAR_NBR])//var __Mon = month(__Dt)var __tmpTable = CALCULATETABLE(    Summarize(        '2 UDM DateTable'        ,'2 UDM DateTable'[CAL_YEAR_NBR]        ,'2 UDM DateTable'[CAL_MONTH_NBR]        ,"Cust Agg",[Customer Count To Date]        ,"CMI Agg",[CMI]        ,"SAIDI Agg",[SAIDI Form]    )    ,filter('2 UDM DateTable','2 UDM DateTable'[CAL_YEAR_NBR]=__Yr))VAR __SubTotal =  sumX(__tmpTable,[SAIDI Agg])returnif(HASONEVALUE('2 UDM DateTable'[CAL_MONTH_NBR]) && HASONEVALUE('2 UDM DateTable'[CAL_YEAR_NBR])    ,[SAIDI Form]    ,if(HASONEVALUE('2 UDM DateTable'[CAL_YEAR_NBR])        ,sumX(__tmpTable,[SAIDI Agg])        ,blank()    ))`

This produces the following:

`Year	SAIDI2019	2.711	15.092	3.993	7.174	5.355	8.556	12.167	10.268	8.129	3.12Total	73.82`

I'm so confused as to why the all causes the 2019 subtotal to show correctly or not. It's so weird. I'm not aware of any other filters on the date table other than the year must be 2019. There is a single record for every date. I just don't get it.

Thanks,
Andy

2 REPLIES 2
Super User IV

## Re: Matrix Totals Not Matching

Sounds like you are potentially following my Quick Measure, Measure Totals, The Final Word:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

If not, maybe it will help. Also, there is MM3TR&R: https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

One other item, you might have been luck with ISINSCOPE instead of HASONEVALUE.

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Member

## Re: Matrix Totals Not Matching

Hey Greg! Thanks for the reply! Yes I've been using your patterns, they are awesome. The inscope behavior is interesting. It makes things work a little better in the matrix visual, but naturally it screws up some of my other visuals. For instance, I'm using a shape map where I'm filtering on 'calendar_date' (one entry per day). I have to use the 'date' data type in order to use the relative date filtering to be in this year. Even if I try to drop 'year_nbr' field into the filter and filter it on 2019, it must be returning more than just the single row by shape, because the value is blank now. Formula below.

```SAIDI =
var __Yr = max('2 UDM DateTable'[CAL_YEAR_NBR])

var __tmpTable =
Summarize(
all('2 UDM DateTable')
,'2 UDM DateTable'[CAL_YEAR_NBR]
,'2 UDM DateTable'[CAL_MONTH_NBR]
,"SAIDI Agg",[SAIDI Form]
)

VAR __SubTotal =
sumX(
FILTER(
__tmpTable
,'2 UDM DateTable'[CAL_YEAR_NBR]=__Yr
),
[SAIDI Agg]
)

return

if(
HASONEVALUE('2 UDM DateTable'[CAL_MONTH_NBR]) && HASONEVALUE('2 UDM DateTable'[CAL_YEAR_NBR])
,[SAIDI Form]
,if(ISINSCOPE('2 UDM DateTable'[CAL_YEAR_NBR])
,__SubTotal
,blank()
)
)```

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors