Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ssai
Frequent Visitor

Wrong calculation of Total for columns in a matrix representation.

ssai_0-1675251469327.png

Hello Community,

I have this data in the above format of a matrix  representation. But the total that is getting calculated and displayed is not correct. Can anyone help me out with this as to why the calculation is going wrong? The bottom most row is showing the total for all the above rows. The same issue lies with both the columns. 

 

2 REPLIES 2
ITManuel
Responsive Resident
Responsive Resident

Hi @ssai ,

 

the filter context in the "Total" line is different than in the matrix itself, depending on the calculation you execute the total is consequently not necessarily the sum of the individual values. 

You will need to sum up all values in a iteration. 

You will find several articles / content about this bevaiviour in the forum and elsewhere, for example https://www.youtube.com/watch?v=dgwsk2fjWLc

If you share the code and some infos of your data model would be useful.

 

Best regards

 

ssai
Frequent Visitor

Type of mobile

 

 

 

Cost

Count

unit cost

abcxxxxx

 

2

 

abcxxxxx

 

1

 

abcxxxxx

187,392.00

105

1536

abcxxxxx

 

1

 

abcxxxxx

 

1

 

abcxxxxx

 

1

 

abcxxxxx

 

1

 

abcxxxxx

 

1

 

abcxxxxx

 

3

 

abcxxxxx

336

2

168

abcxxxxx

 

1

 

abcxxxxx

 

1

 

abcxxxxx

 

20

 

abcxxxxx

420

5

84

abcxxxxx

 

1

 

abcxxxxx

 

7

 

abcxxxxx

 

7

 

abcxxxxx

 

1

 

abcxxxxx

 

4

 

abcxxxxx

 

7

 

abcxxxxx

 

1

 

abcxxxxx

 

1

 

abcxxxxx

1296.00

6

216

abcxxxxx

 

3

 

abcxxxxx

 

1

 

abcxxxxx

 

7

 

abcxxxxx

 

1

 

abcxxxxx

 

1

 

Total

71,838,948.71

134

 

 

First of all thanks for reaching out. Below are the details.

The table above is a sample of what I was working with. The cost column is actually a measure that multiplies the unique count with the unit cost. But the total cost which is getting displayed is wrong when compared with manually computed figures.

Cost = FORMAT(SUM('tablename'[Unitary Cost (cost per unit yearly)])*SUMX('tablename (2)','tablename (2)'[distinctcount]), "Standard")
For distinct count : I am using a measure called distinct count inside this cost measure. 
These are the details. I wanted to know why the total column shows wrong values and is this the right DAX query to apply? Total cost is 71,848,938.71 and the total count is 134.
Thanks in advance ::))

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors