Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Matrix Column Subtotals much too high - not actual...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Matrix Column Subtotals much too high - not actually summing column values

01-04-2021
09:42 AM

Hello,

I will preface this with I understand exactly what is wrong, I am just not sure how to fix it. I have looked at various posts on this forum and have found none that quite match my issue.

I have a matrix with several pairs of columns in which I show summed subtotals for license counts and license costs - a user can have multiple licenses, which is represented in the 'ChargeBackReport_Static' table, and each of those licenses has a cost, represented in the 'LicenseDetail' table. I have a one to many relationship on 'License ID' between those two tables.

A user is also a part of a department, which is a part of a company, so we want the matrix to summarize by those for each license (which I have in the 'Rows' on the matrix), for both count of licenses and associated summed cost of that set of licenses in each of the columns. Each license has a column for count and column for cost, which are my measures in 'Values':

Count = Countrows(ChargeBackReport_Static)

and

Cost = Calculate([Count] * SUM((LicenseDetail[LicenseCost]))),

where 'ChargeBackReport_Static' is my primary table of user/department/company license data, and LicenseCost is my reference table that keeps track of each license's cost.

For example:

Company User License ID

ABC john.doe Dynamics AX

ABC john.doe Microsoft Project

ABC jane.doe Microsoft Project

DEF john.smith Dynamics AX

DEF john.smith Microsoft Project

License ID Cost

Dynamics AX $100.00

Microsoft Project $10.00

The columns are all the proper values - these measures are counting and summing properly. However, the 'column subtotal' - which I expected to total the values of each of the columns - is instead summing the cost of each **individual** license, **and then** multiplying by the count of the licenses, instead of just taking the sum of the **summed cost** of each license.

I understand why this is happening - because I simply have Sum(LicenseCost), if that particular company/department (whatever summary we are doing) has X users with ABC license, and Y users with DEF license, it will take (cost of ABC license + cost of DEF License) * (X users + Y users), whereas I want it to do (cost of ABC license * X users) + (cost of DEF License * Y users).

How would I adapt my measure to get around this? Or is there a way to simply add my own manually calculated 'total' column onto the end of the matrix?

I have attached a screenshot of my report so you can do the sums for yourself across the columns and see the discrepancy, but I can also attach the actual pbix file - it does not contain any sensitive data.

Thanks so much for any help you can provide!

All the best,

Alex

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-04-2021
10:43 AM

Found it. What your measure is taking the count from each cell in the matrix and multiplying it by the sum of license costs, which is why everything is so overinflated.

Try the measures I posted.

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-04-2021
10:38 AM

With the sample data you provided, would you expect this as the result?

Measures...

```
License Count = COUNTROWS(Licenses)
Total Cost = SUM('License Costs'[Cost])
Extended Cost = [License Count] * [Total Cost]
```

Could you post the DAX for the measure you're using so I can look at it?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-04-2021
10:43 AM

Found it. What your measure is taking the count from each cell in the matrix and multiplying it by the sum of license costs, which is why everything is so overinflated.

Try the measures I posted.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-04-2021
11:52 AM

Hi @littlemojopuppy ,

So the sample data I gave you actually did not tell the whole story - sorry about that. So while your solution did not quite work for me (thought it would likely work for most), I realized that what I was doing wrong was indeed doing the calculation in the same measure, and including a field that needed to be summarized **from another table. **

The solution for me was to actually pull the 'License Cost' field in from the LicenseDetail table into my main table with Merge Queries, and then my measures became extremely simple - just Count = Countrows(ChargeBackReport_Static) and Cost = SUM(ChargeBackReport_Static[LicenseCost]). That way, it isn't having to sum and multiply by the count in one step, and is more obliged to do what it is intended to do.

I will still mark yours as the solution since it inspired my solution, and post this in case anyone else runs into something similar.

Thanks so much for your help!

Best,

Alex

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-04-2021
11:55 AM

Glad I could help! 🙂

Announcements

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Featured Topics

Top Solution Authors

User | Count |
---|---|

230 | |

133 | |

87 | |

64 | |

51 |

Top Kudoed Authors

User | Count |
---|---|

249 | |

166 | |

103 | |

81 | |

76 |