Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
Hi @Anonymous ,
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.
Best Regards,
Cherry
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
also my what if parameter is a percentage, for example a 5 % price raise on the current price.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |