Filtering in a formule with slicers

[janwillem]

I have made a formule for Offer Agreement as follow: 

COUNT ('om_orders'[Ordernr.]) /

CALCULATE (

                      COUNT ('om_orders'[Offertenr.]);

                      'om_orders'[type] = 10

                     ) 

I have also slicers for Productgroup and Item and I will use them for this calculation.

At the moment when I choose a Productgroup nothing change.

How can I fix this.

[technolog · Accepted solution]

In Power BI, slicers typically interact with all visuals and formulas on a page. However, your formula is using the CALCULATE function, which can modify the default filter context, so that might be influencing how it responds to slicers.

If you want your formula to respect the slicers on Productgroup and Item, ensure those fields have proper relationships set up with the om_orders table, and also make sure that you're not overriding those filters in your DAX formulas.

Your formula divides the total order count by a count of offers with a specific type. If you want the formula to respond to slicers on Productgroup and Item:

Make sure you have relationships set up between om_orders and the tables containing Productgroup and Item. This is done in the "Model" view in Power BI Desktop.

Modify your formula to not override those filters. Here's the revised formula:

Offer Agreement Ratio =
COUNT('om_orders'[Ordernr.]) /
CALCULATE(
COUNT('om_orders'[Offertenr.]),
'om_orders'[type] = 10,
ALLSELECTED() // This ensures you're keeping the slicers context
)
By using ALLSELECTED(), you respect the slicers' selections while still applying the specific filter 'om_orders'[type] = 10.

However, if you're still facing the issue, double-check the relationships in your data model. Ensure that there are no other formulas or visuals that might be altering the filter context in a way you didn't anticipate.