cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ahaugstad
New Member

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

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:
ChargeBackReport_Static:
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 Cost:
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

 

PowerBIMatrixColumnSubtotals.PNG

 

 
 

 

 

1 ACCEPTED SOLUTION

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.

View solution in original post

4 REPLIES 4
littlemojopuppy
Super User I
Super User I

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

littlemojopuppy_0-1609785398077.png

 

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?

 

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.

View solution in original post

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

Glad I could help!  🙂

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

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

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors