Incorrect Measure Total

[Erwin]

Dear all,

Please review below table.

Order          Object       Amount

1	A	10
2	A	15
3	A	5
4	A	20
4	B	5
4	C	10
4	D	25
5	B	5
6	B	5
7	C	10
8	D	35
9	D	15
10	C	5

I would like to filter out all orders with a relation to more than 1 object. As you can see this is only true for Order #4.

I have created the following measure:

Sum of Orders >1 Object :=
SUMX (
    FILTER ( Data, COUNTROWS ( VALUES ( Data[Object] ) ) > 1 ),
    Data[Amount]
)

This code works, as it filters out Order #4 when orders are expanded in the pivottable. However, the measure total still shows the total value of the Amount column. Obviously this is due to the fact that, in the total row, there is no context for Order and the expression is calculated over the entire table.

I know I need to use HASONEVALUE to be able to write different code for the calculation of the total value. I just can't come up with the correct code.

Sum of Orders >1 Object:=IF(
HASONEVALUE(Data[Order]),
        SUMX (
             FILTER ( Data, COUNTROWS ( VALUES ( Data[Object] ) ) > 1 ),
             Data[Amount]
         ),
        ?????? What goes here???
)

Can you help me with this?

Rg. Erwin

[sokg]

What number to you expect to show in total???

My guess is that you want nothing so you write

BLANK()

[Zubair_Muhammad · Accepted solution]

@Erwin

Try creating another measure as follows

Measure =
IF (
    HASONEVALUE ( Data[Order] ),
    [Sum of Orders >1 Object],
    SUMX ( VALUES ( Data[Order] ), [Sum of Orders >1 Object] )
)

[Erwin]

That did the trick! Guess I was thinking in circles and it was actually simpler than I thought.

Thanks!

Erwin

[sokg]

Glad to help you!!!