Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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.
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.
Thank you, exactly what I was looking to accomplish.
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?
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.
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.
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,
@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))
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)
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.