cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GraceTCL
Helper II
Helper II

Compare Total Platform vs Segment margins

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:

Segment.PNG

 

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!

1 ACCEPTED SOLUTION
DataInsights
Super User II
Super User II

@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

 

DataInsights_0-1619014971939.png

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] )".

View solution in original post

1 REPLY 1
DataInsights
Super User II
Super User II

@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

 

DataInsights_0-1619014971939.png

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] )".

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors