Measure with conditional sum or percentage

[sekic]

I try to get percentage of totals, but matrix show total all percentage.

Matrix show total Revenue, Expenses, Profit and percent of profit over revenue.

Can't find any idea how to form measure with condition to avoid sumimg percentages.

Category Name        Dept    Total
1        Revenue             1200
                     D1       700
                     D2       500
2        Exp                 1000
                     D1       800
                     D2       200
3        Profit               200
                     D1      -100
                     D2       300
4        Profit %            45.7 there is sum(-14.3+60.0)
                                  instead 16.7% as Profit % of total profit

                                  over total Revenue (as 200/1200)
                     D1     -14.3 (as -100/700)
                     D2      60.0 (as 300/500)

[v-yulgu-msft · Accepted solution]

Hi @sekic,

The "Total" column in Matrix sums up detailed records per row group, it is not able to change this default behavior to make it do a different calculation for some specific groups.

As a workaround, you can try this measure:

Total = SUM(Test4[Amount])

Sub total =
IF (
    SELECTEDVALUE ( Test4[Name] ) = "Profit%",
    CALCULATE (
        SUM ( Test4[Amount] ),
        FILTER ( ALL ( Test4 ), Test4[Name] = "Profit" )
    )
        / CALCULATE (
            SUM ( Test4[Amount] ),
            FILTER ( ALL ( Test4 ), Test4[Name] = "Revenue" )
        ),
    CALCULATE ( SUM ( Test4[Amount] ), ALL ( Test4[Dept] ) )
)

Best regards,

Yuliana Gu