March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 1 | Fire System | Product 1 | 5000 | 1000 |
Customer 1 | Water System | Product 2 | 10000 | 2000 |
Customer 1 | Air System | Product 3 | 15000 | 3000 |
Customer 2 | Fire System | Product 1 | 6000 | 1000 |
Customer 2 | Fire System | Product 2 | 6000 | 2000 |
Customer 2 | Air System | Product 4 | 16000 | 4000 |
Customer 3 | Water System | Product 5 | 11000 | 5000 |
Customer 3 | Water System | Product 6 | 11000 | 6000 |
Customer 3 | Air System | Product 4 | 16000 | 4000 |
Customer 1 | Air System | Product 5 | 15000 | 5000 |
Sample Pivot Arrangement: Proper Grand Total System Cost should be 79,000
Row Labels | Sum of System Cost | Sum of Product Cost |
Customer 1 | 45000 | 11000 |
Fire System | 5000 | 1000 |
Product 1 | 5000 | 1000 |
Water System | 10000 | 2000 |
Product 2 | 10000 | 2000 |
Air System | 30000 | 8000 |
Product 3 | 15000 | 3000 |
Product 5 | 15000 | 5000 |
Customer 2 | 28000 | 7000 |
Customer 3 | 38000 | 15000 |
Grand Total | 111000 | 33000 |
Thank you for your help,
Solved! Go to Solution.
Hi @DangerMan
Try the following measure; it gives the right subtotals and totals.
Hi @DangerMan
Try the following measure; it gives the right subtotals and totals.
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,
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!
"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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
27 | |
17 | |
16 | |
12 | |
11 |
User | Count |
---|---|
35 | |
26 | |
26 | |
20 | |
15 |