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

[ahaugstad]

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

[littlemojopuppy]

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?

[littlemojopuppy · 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.

[ahaugstad]

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

[littlemojopuppy]

Glad I could help!  🙂