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!
Go to Solution.
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
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
Check out new user group experience and if you are a leader please create your group!
Check out how to claim yours today!
Test your skills now with the Cloud Skills Challenge.