Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
soldstatic
Resolver I
Resolver I

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
Greg_Deckler
Super User
Super User

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. 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.