cancel
Showing results for
Did you mean:
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
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]))``

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])
``````

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

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.

7 REPLIES 7
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]))``

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])
``````

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

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.

Frequent Visitor

Thank you, exactly what I was looking to accomplish.

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?

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)
``````

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.

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.

Frequent Visitor

@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.

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,

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

Proud to be a Super User!

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)

---------------------------------------

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

Proud to be a Super User!

Frequent Visitor

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.

Announcements