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.
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.
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.
More details, please refer to my attachement.