What If Calculation Based on Dynamic Filter

Hi rawiswarden ,

Can this meet your requirements?

Steps:

1. Enter a Filter table like so:

2. Create measures.

LastMonthProfit = 
VAR LastMonth =
    DATEADD ( 'Table'[Date], -1, MONTH )
RETURN
    CALCULATE (
        MAX ( 'Table'[Profit] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] = LastMonth
                && 'Table'[Customer] = MAX ( 'Table'[Customer] )
        )
    )

Profit Ratio = DIVIDE ( SUM ( 'Table'[Profit] ), SUM ( 'Table'[Sales] ) ) 

Profit Ratio - Last Month Profit <> 0 = 
CALCULATE (
    DIVIDE ( SUM ( 'Table'[Profit] ), SUM ( 'Table'[Sales] ) ),
    FILTER ( 'Table', [LastMonthProfit] <> 0 )
)

Profit Ratio - This Month Profit <> 0 = 
CALCULATE (
    DIVIDE ( SUM ( 'Table'[Profit] ), SUM ( 'Table'[Sales] ) ),
    'Table'[Profit] <> 0
)

Profit Measure =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Filter Table'[With / Without Customer] ) = "With Customers", SUM ( 'Table'[Profit] ),
    SELECTEDVALUE ( 'Filter Table'[With / Without Customer] ) = "Without Customers"
        && SELECTEDVALUE ( 'Filter Table'[Filter] ) = "This Month Profit <>0", CALCULATE ( SUM ( 'Table'[Profit] ), 'Table'[Profit] <> 0 ),
    SELECTEDVALUE ( 'Filter Table'[With / Without Customer] ) = "Without Customers"
        && SELECTEDVALUE ( 'Filter Table'[Filter] ) = "Last Month Profit <>0", CALCULATE (
        SUM ( 'Table'[Profit] ),
        FILTER ( 'Table', [LastMonthProfit] <> 0 )
    )
)

Sales Measure =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Filter Table'[With / Without Customer] ) = "With Customers", SUM ( 'Table'[Sales] ),
    SELECTEDVALUE ( 'Filter Table'[With / Without Customer] ) = "Without Customers"
        && SELECTEDVALUE ( 'Filter Table'[Filter] ) = "This Month Profit <>0", CALCULATE ( SUM ( 'Table'[Sales] ), 'Table'[Profit] <> 0 ),
    SELECTEDVALUE ( 'Filter Table'[With / Without Customer] ) = "Without Customers"
        && SELECTEDVALUE ( 'Filter Table'[Filter] ) = "Last Month Profit <>0", CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( 'Table', [LastMonthProfit] <> 0 ) )
)

Profit Ratio Measure = 
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Filter Table'[With / Without Customer] ) = "With Customers", [Profit Ratio],
    SELECTEDVALUE ( 'Filter Table'[With / Without Customer] ) = "Without Customers"&&SELECTEDVALUE('Filter Table'[Filter])="This Month Profit <>0", [Profit Ratio - This Month Profit <> 0],
    SELECTEDVALUE ( 'Filter Table'[With / Without Customer] ) = "Without Customers"&&SELECTEDVALUE('Filter Table'[Filter])="Last Month Profit <>0", [Profit Ratio - Last Month Profit <> 0]
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.