Forum Discussion

rawiswarden

Helper I

6 years ago

Solved

What If Calculation Based on Dynamic Filter

Hello,

In DAX, is there any way to dynamically create a list to filter a table?

For example, say I wanted to generate a list of Customer based on a rule. An example rule could be that the customers had Profit equal to zero. I want to sum the amount of sales and profit excluding those Customers and show what the results would look like if we did not have those Customer relationships.

This would be a dynamic "What If" analysis based on the rule to generate the list.

I want to show how the removal of certain customers would impact financial ratios. So for example, I take customers with a low profit margin ( profit / sales ) and show a graph comparing the profit metrics with and without the customers. Once I have the data in calculations I can visualize the scenarios next to each other.

An example table.

Customer	Sales	Profit
A	22	0
B	20	5
C	38	0
D	40	15
E	12	0
F	34	5
G	28	0
H	20	10
Total	214	35

Result table for a point in time:

	With Customers	Without Customers
Sales	214	114
Profit	35	35
Profit Ratio	16.4%	30.7%

But of course if there were dates in the data set, I'd want to see it aggregated by date as well.

Need Help

Icey

6 years ago

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.

What If Calculation Based on Dynamic Filter.pbix67 KB

3 Replies

Icey

Community Support

6 years ago

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.

What If Calculation Based on Dynamic Filter.pbix67 KB

parry2k
Super User
6 years ago
rawiswarden you can achieve whatever you want, you need to apply business logic in your measures and build your visuals on top of it, in this case your sales for non profit customers ca be achieved using following measure and then you can create %

so all of this is achievable. Would appreciate Kudos 🙂 if my solution helped.

Sales of no profit customers = CALCULATE ( SUM ( Table[Sales] ), Table[Profit] <> 0 )
- rawiswarden
  Helper I
  6 years ago
  Hello,
  
  That works when there isn't a time dimension. But say I wanted to exclude customers who had 0 profits this month, but may have had profits last month. So I use some rule to determine who to exclude and then exclude them from everything. Does that make sense?

Forum Discussion

What If Calculation Based on Dynamic Filter

3 Replies

Related content

Dynamic Filter

Calculating with Filters

Dynamic Rank Calculation

Dynamic Filtering

Dynamic Calculation Different Period