CODEWITHSUNDEEP
powerbidax
Sonali
Sonali

Reputation: 2273

Total/Sum working incorrectly in Power Bi

I have created a Report in which I have created some measures like -

X = 
CALCULATE (
    DISTINCTCOUNT ( ActivityNew[Name] ),
    FILTER (
        ActivityNew,
        ActivityNew[Resource Owner Name] = MAX ( 'Resource Owners'[Manager Name] )
            && ActivityNew[LocationId] = 2
    )
)

When I use this measure in table then the column values dont add up. For eg. if the value of this measure is 2,2,2,2,2 then Total in table should be 10. but it is showing 2.

I have noticed that wherever I have used this MAX(), the measure values are not adding up.

Why this is happening and Is their any solution for this?

Upvotes: 0

Views: 10533

Answers (1)

Dan
Dan

Reputation: 10680

You are using DISTINCTCOUNT which is in general not aggregatable.

Say you have the following table Sales:

+----------+------+-------+
| Customer | Item | Count |
+----------+------+-------+
| Albert   | Coke | 3     |
| Bertram  | Beer | 5     |
| Bertram  | Coke | 2     |
| Charlie  | Beer | 1     |
+----------+------+-------+

If you wanted to count the number of distinct items each customer has bought, you would create a new measure with the formula:

[Distinct Items] := DISTINCTCOUNT(Sales[Item])

If you include the [Customer] column and your [Distinct Items] measure in a report, it would output the following:

+----------+----------------+
| Customer | Distinct Items |
+----------+----------------+
| Albert   | 1              |
| Bertram  | 2              |
| Charlie  | 1              |
+----------+----------------+
| Total    | 2              |
+----------+----------------+

As you can see, this does not sum up, as the context of the total row, is the entire table, not filtered by any particular customer. To change this behaviour, you have to explicitly tell your measure that it should sum the values derived at the customer level. To do this, use the SUMX function. In my example, the measure formula should be changed like this:

[Distinct Items] := SUMX(VALUES(Sales[Customer]), DISTINCTCOUNT(Sales[Item]))

As I only want to sum over unique customers I use VALUES(Sales[Customer]). If you want to sum over every row in the table simply do: SUMX(<table name>, <expression>).

With this change, the output in the above example would be:

+----------+----------------+
| Customer | Distinct Items |
+----------+----------------+
| Albert   | 1              |
| Bertram  | 2              |
| Charlie  | 1              |
+----------+----------------+
| Total    | 4              |
+----------+----------------+

Upvotes: 4

Related Questions