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

[DangerMan]

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, 

[saraMissBI · Accepted solution]

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

[DangerMan]

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!

[saraMissBI]

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,

[DangerMan]

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

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

[DangerMan]

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.