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
Anonymous
Not applicable

Matrix Grand Totals In Each Row by Drill Down and Slicer Selections

I have been searching for awhile and cant come up with a solution.   I have a table with a calculated column showing a $ value per line in the raw data.  What I am looking to do is create a measure that shows the sum(total $) amount based at the current drill down in the matrix table and user selected slicers.   Measure also needs to be smart enough to identify which filters the user selects.  available slicers come from 3 tables sales data, date table, and consolidated site). 

 

The examples I have seen online are for tables with 1 or 2 columns in the martix and rest are values.  Current Measure I have returns the grand total amount in each row, so if I have site and customer name in the column headers, the measure does not return the value by site/customer and user selected slicers.  

 

Reason for this is that I need a way to omit customers with net income of $0 and sort net income ascending. Just filtering by the value field doesn't show the result I want.  

 

 

Newmeasure = 
VAR _cust =
    ALLSELECTED ( 'table1'[Customer Name] )
VAR _Site =
   ALLSELECTED ( 'table2'[Location Name] )
RETURN
    CALCULATE (
        SUM ( 'table1'[Value] ),
        ALL ( 'table1' ),
        'table1'[Customer Name] IN _cust,
        'table2'[Location Name] IN _Site

 

 

1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

Don't @sicbi,

You must create a calculation table that contains the entire value. Because white space means there is no value.

See the following steps.

1. Create a calculation table and add a value column.

Table 2 = 
CROSSJOIN(
    VALUES('Table'[Customer]),
    VALUES('Table'[Location]))

Value = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Location]='Table 2'[Location]))

ma1.jpg

2. Next, create two measures. Slicers must use the fields in Table 2.

Table value = 
var _cus = VALUES('Table 2'[Customer])
var _loc = VALUES('Table 2'[Location])
return
IF(
    ISINSCOPE('Table 2'[Customer]) && ISINSCOPE('Table 2'[Location]),
    CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Customer]=MAX('Table 2'[Customer]) && 'Table'[Location]=MAX('Table 2'[Location]))),
    CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Customer] in _cus && 'Table'[Location] in _loc)))

Table 2 value = 
SUM('Table 2'[Value])

ma2.jpg

Or you can pivot the table to this structure. You can then add the column that has no white space.

ma3.jpg

If you don't meet your requirements, could you show the exact expected result based on the table you shared?

Best regards

Community support team _ zhenbw

If this post helps,then consider Accepting it as the solution to help other members find it faster.

BTW, pbix as an attachment.

View solution in original post

7 REPLIES 7
v-zhenbw-msft
Community Support
Community Support

Don't @sicbi,

You must create a calculation table that contains the entire value. Because white space means there is no value.

See the following steps.

1. Create a calculation table and add a value column.

Table 2 = 
CROSSJOIN(
    VALUES('Table'[Customer]),
    VALUES('Table'[Location]))

Value = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Location]='Table 2'[Location]))

ma1.jpg

2. Next, create two measures. Slicers must use the fields in Table 2.

Table value = 
var _cus = VALUES('Table 2'[Customer])
var _loc = VALUES('Table 2'[Location])
return
IF(
    ISINSCOPE('Table 2'[Customer]) && ISINSCOPE('Table 2'[Location]),
    CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Customer]=MAX('Table 2'[Customer]) && 'Table'[Location]=MAX('Table 2'[Location]))),
    CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Customer] in _cus && 'Table'[Location] in _loc)))

Table 2 value = 
SUM('Table 2'[Value])

ma2.jpg

Or you can pivot the table to this structure. You can then add the column that has no white space.

ma3.jpg

If you don't meet your requirements, could you show the exact expected result based on the table you shared?

Best regards

Community support team _ zhenbw

If this post helps,then consider Accepting it as the solution to help other members find it faster.

BTW, pbix as an attachment.

Anonymous
Not applicable

Thank you, exactly what I was looking to accomplish.

v-zhenbw-msft
Community Support
Community Support

Hi @Anonymous ,

 

Do Table 1 and Table 2 have a relationship?

Do you want to get the Total that contains selected customer and location in the grand total?

 

matrix1.jpg

 

If yes, we can use the following measure to meet your requirement.

 

Measure = 
var _sum = CALCULATE(SUM('Table'[Value]),ALLSELECTED('Table'))
return
IF(
    ISINSCOPE('Table'[Customer]) && ISINSCOPE('Table'[Location]),
    SUM('Table'[Value]),
    _sum)

 

matrix2.jpg

 

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data?

It will be helpful if you can show us the exact expected result based on the tables.

 

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

 

Best regards,

 

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

BTW, pbix as attached.

Anonymous
Not applicable

@v-zhenbw-msft that is very close to what I want. I want 2 results in the values field, the 1st result would be what your showing the example which is what I have. What I am trying to get in a seperate column next to that (highlighed in yelow in the picture) is the grand total shown in each row for that specific customer and location. There is also another heirarchy level down in the martix from your view to account for. Each heirarchy the user is on will show the same result, the total shown on each row.

Function ISINSCOPE() is what I think is needed to get what I am after, just need to piece it together.

Everything I have read online about the measure I need, doesnt take into account for slicers on the page and multiple heirarchy levels in the matrix table.

example.png

Sorry I am new, is there a guide on the site here how to upload a file for onedrive for business? Just want to make sure what and were is post is not tied to my company. Sorry for the rookie question,

amitchandak
Super User
Super User

@Anonymous , Try measure like

Newmeasure =
VAR _cust =
ALLSELECTED ( 'table1'[Customer Name] )
VAR _Site =
ALLSELECTED ( 'table2'[Location Name] )
RETURN
CALCULATE (
SUM ( 'table1'[Value] ),
filter(ALL ( 'table1' ),
'table1'[Customer Name] IN _cust),
filter(ALL ( 'table2' ),'table2'[Location Name] IN _Site))

Greg_Deckler
Super User
Super User

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

 

If this doesn't get you what you need, please provide sample data and expected output.

Also, this may be of assistance:

Matrix Measure Total Triple Threat Rock & Roll (powerbi.com)

 

 


@ 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...
Anonymous
Not applicable

Greg I dont think that solution will work for me.  What I am trying to get is a measure that shows me the grand total of the current matrix view.  Matrix can be top level, drilled down or users select slicers.  Maybe there is a better way to get what I am after with omotting locations with a grand total of $0? 

 

Below is a sample table in excel.  Columns in yellow is the new measure I am working on.  The measure will report the grand total amount in each row.  I figured this way I can use the measure as a filter and a way to sort by.

 

Capture.PNG

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.