cancel
Showing results 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.

Frequent Visitor

Wrong calculation of Total for columns in a matrix representation.

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
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.

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

Best regards

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.

Announcements

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

Power BI Monthly Update - April 2024

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

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors