cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
soldstatic Member
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])

return

if(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	SAIDI
2019 73.82
1 15.09
2 3.99
3 7.17
4 5.35
5 8.55
6 12.16
7 10.26
8 8.12
9 3.12
Total 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])

return

if(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	SAIDI
2019 2.71
1 15.09
2 3.99
3 7.17
4 5.35
5 8.55
6 12.16
7 10.26
8 8.12
9 3.12
Total 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
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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

soldstatic Member
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()
            )
        )

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

January 2020 Community Highlights

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

Top Solution Authors