Measure totals in matrix with hierarchy

[Anonymous]

Hi all,

I've got such a matrix.

Rows: Channel, Customer, Segment; Columns: Year-Period; Values: MyMeasure = IF(Table[Amount]<0;0;Table[Amount]).
Surely, PBI calculates totals not as I expect it. As you can see the sum of Segments doesn't equal Customer's subtotals.

	2019-P09	2019-P10
Channel-1	70	75
Customer-1	50	45
Segment-1	10	10
Segment-2	20	10
Segment-3	30	30
Segment-4	0	0
Customer-2	20	30
Segment-1	10	15
Segment-2	0	0
Segment-3	20	20

I tried this measure: SUMX(FILTER(VALUES(Table[Segment]);Table[Amount]>0);Table[Amount])+0

Now I have correct subtotals at the level of Customer, but at the Channel level of hierarchy subtotals are still incorrect.

	2019-P09	2019-P10
Channel-1	70	75
Customer-1	60	50
Segment-1	10	10
Segment-2	20	10
Segment-3	30	30
Segment-4	0	0
Customer-2	30	35
Segment-1	10	15
Segment-2	0	0
Segment-3	20	20

Please, help me to write the correct DAX for this matrix.

[technolog · Accepted solution]

To address the issue with correct totals at different levels of the hierarchy in your matrix, you can use the SUMX function to iterate over the values properly at each level. The challenge is ensuring that the total at each level (Channel, Customer, Segment) respects the conditions you set for the measure.

Here is how you can adjust your measure:

MyMeasure = 
SUMX(
    VALUES(Table[Segment]),
    IF(Table[Amount] < 0, 0, Table[Amount])
)

If this does not give the desired results, you can adjust it to handle each hierarchy level by checking the context. This is a more advanced solution using ISINSCOPE:

MyMeasure = 
IF(
    ISINSCOPE(Table[Segment]),
    IF(Table[Amount] < 0, 0, Table[Amount]),
    SUMX(
        VALUES(Table[Segment]),
        IF([Total Amount] < 0, 0, [Total Amount])
    )
)