Compare Total Platform vs Segment margins

[GraceTCL]

Hi!

I am trying to compare the margins of the business segment vs the Total platform where the business segment is a subset of the Platform.

This comparison is intended via a waterfall chart.

When I filter for the segment on the waterfall chart visual, unfortunately, the platform is also filtered where "platform" = segment and the margins are the same.

This is despite the fact that I have computed the margins for the platform using CALCULATE and FILTER (for all segments) function while the margins for the segment is subjected to filtered conditions.

Could you let me know if above comparison is feasible through a single visual and if so, what can I do to achieve above outcomes?

To elaborate more on my problem above. I think one hypothesis might be that my formula for getting sum of platform is not independent of filter conditions.

Using this hypothetical eg where:

Table and info as per screenshot:

My dax formula:

Revenue_measure = sum('Sales'[Revenue])

Platform_Revenue = CALCULATE([Revenue_measure],'Sales'[Platform]="A")

When I filter for Segment AD, unfortunately, Platform_revenue = "AD" and not Sum of "A".

Pls help to advise how should I correct my formula. Thanks!

[DataInsights · Accepted solution]

@GraceTCL,

Try these measures:

Sum Profit = SUM ( Sales[Profit] )

Sum Revenue = SUM ( Sales[Revenue] )

Margin Segment vs Platform = 
VAR vSegmentMargin =
    DIVIDE ( [Sum Profit], [Sum Revenue] )
VAR vPlatformProfit =
    CALCULATE ( [Sum Profit], ALL ( Sales[Segment] ), VALUES ( Sales[Platform] ) )
VAR vPlatformRevenue =
    CALCULATE ( [Sum Revenue], ALL ( Sales[Segment] ), VALUES ( Sales[Platform] ) )
VAR vPlatformMargin =
    DIVIDE ( vPlatformProfit, vPlatformRevenue )
VAR vResult = vSegmentMargin - vPlatformMargin
RETURN
    vResult

You were on the right track with the Platform_Revenue measure, but it needs to be dynamic. This is achieved with the CALCULATE filter "VALUES ( Sales[Platform] )".