cancel
Showing results for
Search instead for
Did you mean:
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 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,

1 ACCEPTED SOLUTION
Resolver I

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
6 REPLIES 6
Resolver I

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
Regular Visitor

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!

Resolver I

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,

Regular Visitor

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!

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.

Regular Visitor

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

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors