cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
robbinkees Frequent Visitor
Frequent Visitor

Whatif parameter on filtered slicer items

Hello All, 

 

I have a matrix in my report where i calculate my marge per productcategory. 

I calculate the marge through a divide of my sales price and my cost price. 

I want to calculate a new marge when i change my Sales price. 

Currently i can change my salesprice with the what-if parameter(image below), but when i do this the what if works on all product categorys. 
Knipsel.PNG

I also have a slicer where i can filter my productcategorys. How can i fix that the new marge based on the what if, only changes on productcategories that i selected in the slicer. The new avg% marge should be based on the old marge of products that are NOT selected in the slicer AND the new marge off the products that are selected in the slicer. 

For example in the data image. I select in the slicer "prouductcategory A" and in my what-f parameter a price change of 10%. only productcatogry A has to get a new value. 

 

PLEASE HELP 

 

 

3 REPLIES 3
Community Support Team
Community Support Team

Re: Whatif parameter on filtered slicer items

Hi @robbinkees ,

For your requirement, please refer to the formulas below to achieve your desired output.

1. Create a spreate table with column product and don't create the relationship with the original table.

Table = SUMMARIZE('Table1','Table1'[Product])

2. Create the measure below.

New marge =
VAR a =
    CALCULATE (
        SUM ( 'Table1'[cost price] ),
        ALLEXCEPT ( Table1, 'Table1'[Product] )
    )
VAR b =
    IF (
        ISFILTERED ( 'Table'[Product] ),
        IF (
            MAX ( 'Table1'[Product] ) = SELECTEDVALUE ( 'Table'[Product] ),
            'new_price'[new_price Value],
            CALCULATE ( MAX ( 'Table1'[new price] ) )
        )
    )
RETURN
    a / b

Here is the output.

Capture.PNG

More details, please refer to my attachement.

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
robbinkees Frequent Visitor
Frequent Visitor

Re: Whatif parameter on filtered slicer items

Not exactly where i am looking for. 
Because now I always have to select one productcategory and i can't select multiple categories. @v-piga-msft 

robbinkees Frequent Visitor
Frequent Visitor

Re: Whatif parameter on filtered slicer items

also my what if parameter is a percentage, for example a 5 % price raise on the current price.