cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sicbi
Frequent Visitor

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.

View solution in original post

Thank you, exactly what I was looking to accomplish.

v-zhenbw-msft
Community Support
Community Support

Hi @sicbi ,

 

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.

@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 IV
Super User IV

@sicbi , 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))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Greg_Deckler
Super User IV
Super User IV

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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.