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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
DangerMan
Regular Visitor

Incorrect Grand Totals - Need a Custom Measure to Filter by Multiple Criteria

Help!

 

I'm stuck trying to get the proper Grand Totals for my data.  I have shared some greatly simplified example data below. 

 

I have System Descriptions that are used across multiple Customers, and different Products that are used in the Systems.  The Products can only be used in one System for each Customer, but they can be in different Systems for different Customers.  

I do not want repeating System Cost to be summed, as the System Cost is fixed regardless of how many Products are associated with the System. 

 

I created the following Measure to get the System Cost, and it works for Customer Subtotals, but not for Grand Totals.  I think this is because the Measure takes an AVERAGE across the different Customers.  I believe I need a second logic function to pull out the individual System Costs for each CUSTOMER.  I have tried using DISTINCT, but I can't get it to work...

 

=SUMX( SUMMARIZE( 'Table1', [System Description], "Unique System Cost", AVERAGE( 'Table1'[System Cost] ) ), [Unique System Cost] )

 

Sample Source Data

 Customer NameSystem DescriptionProduct DescriptionSystem CostProduct Cost

Customer 1Fire SystemProduct 150001000
Customer 1Water SystemProduct 2100002000
Customer 1Air SystemProduct 3150003000
Customer 2Fire SystemProduct 160001000
Customer 2Fire SystemProduct 260002000
Customer 2Air SystemProduct 4160004000
Customer 3Water SystemProduct 5110005000
Customer 3Water SystemProduct 6110006000
Customer 3Air SystemProduct 4160004000
Customer 1Air SystemProduct 5150005000

 

Sample Pivot Arrangement:  Proper Grand Total System Cost should be 79,000

Row LabelsSum of System CostSum of Product Cost
Customer 14500011000
Fire System50001000
Product 150001000
Water System100002000
Product 2100002000
Air System300008000
Product 3150003000
Product 5150005000
Customer 2280007000
Customer 33800015000
Grand Total11100033000

 

Thank you for your help, 

 

1 ACCEPTED SOLUTION
saraMissBI
Resolver I
Resolver I

Hi @DangerMan 

Try the following measure; it gives the right subtotals and totals.

 

Total System Cost = if(ISBLANK(SELECTEDVALUE('Table'[System Description])),
SUMX(SUMMARIZE('Table',[Customer Name],[System Description],"customer", SELECTEDVALUE('Table'[Customer Name]),"system cost",max('Table'[System Cost])),[system cost]),
CALCULATE(MAX('Table'[System Cost])))
 
Check out the pbix here.
 
Please if my solution solves the problem you posted, please mark as solution and give kudos! 
Thank you

View solution in original post

6 REPLIES 6
saraMissBI
Resolver I
Resolver I

Hi @DangerMan 

Try the following measure; it gives the right subtotals and totals.

 

Total System Cost = if(ISBLANK(SELECTEDVALUE('Table'[System Description])),
SUMX(SUMMARIZE('Table',[Customer Name],[System Description],"customer", SELECTEDVALUE('Table'[Customer Name]),"system cost",max('Table'[System Cost])),[system cost]),
CALCULATE(MAX('Table'[System Cost])))
 
Check out the pbix here.
 
Please if my solution solves the problem you posted, please mark as solution and give kudos! 
Thank you

Hi @saraMissBI :

 

Your solution works brilliantly!  😁  I wish that I understood the logic, but I am not very well-versed in the DAX functions. 🙁

 

One caveat, my version of Excel did not recognize the SELECTEDVALUE function.  I was able to solve this problem using the equivalent code posted here.  Any reason why this function isn't available on my machine?

 

I also changed the assigned table name from "system cost" to "SYS COST" just to make it easier to keep track of.  

 

Thank you so much for your help!

Hi @DangerMan,

 

You are welcome! Glad you could solve it, but the link does not show the equivalent code you used (the link just opens the page for selectedvalue function). Could you please share the equivalent code you used? it would be interesting to learn alternative solutions.

Also, I read that Selectedvalue() is not supported in Excel yet...(that was in June 2019) 

 

Thank you,

@saraMissBI 

 

The equivalent code is mentioned in the "remarks" section of the SELECTEDVALUE page, and I'll paste it here as well. 

 

An equivalent expression for SELECTEDVALUE(<columnName>, <alternateResult>) is: 

 

IF(HASONEVALUE(<columnName>), VALUES(<columnName>), <alternateResult>)

 

Thanks again for your wonderful solution!

lbendlin
Super User
Super User

"I do not want repeating System Cost to be summed, as the System Cost is fixed regardless of how many Products are associated with the System. "

 

That is a very unfortunate requirement.  Your best bet is to use entirely different data tables for System costs and for Product costs.  Use a data model instead of a single table.

To summarize my need more succinctly, I want the distinct, or unique system cost, regardless of how many products are in the system.  And I want this for each customer. 

 

I would think this is possible with my data structure, because I have seen Measures created to solve the incorrect Grand Total problems.  In this case, I am simply looking at criteria from two different columns. 

 

Edit:  My data is in a Data Model, but I tried to show it very simplified here.  System Cost and Product Cost do come from separate tables and are merged using Power Query. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors