Change Value with Slicer // dynamic Value

[Power_MN]

Hello everyone, I am facing a big problem and I hope that someone here can help me with their expertise. I have created a report that compares a certain price, which is entered by the responsible department in an Excel file, with other prices (ranking, tabular display). The price should no longer be entered via the Excel file, but should be set in the report itself using a slicer. Thus, the consumer of the report can set the price directly in the report and does not always have to enter the price in the Excel file and ask for an refresh of the report. Thank you very much for your ideas and help! Kind regards and a nice day Michael

[v-yingjl · Accepted solution]

Hi @Power_MN ,

Use what-if parameters and try to create measures like this:

Initial rank = 
RANKX(
    ALL('Table'),
    CALCULATE(SUM('Table'[Price])),,DESC,Dense
)


New Rank = 
IF(
    SUM('Table'[Price]) > 'Price reference'[Price reference Value], [Initial rank],
    RANKX(
        FILTER(
            ALL('Table'),
            'Table'[Price] <= 'Price reference'[Price reference Value]
        ),CALCULATE(SUM('Table'[Price])),,DESC,Dense
    )
)

Attached a sample file in the below, hopes it could help.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Power_MN]

@v-yingjl 

Works great! Thank you so much!

Kind regards and stay healthy!

Michael

[selimovd]

Hey @Power_MN ,

you can use the What-If-Parameter to get a range:

Use what-if parameters to visualize variables - Power BI | Microsoft Docs

In the measures you can then calculate with the SELECTEDVALUE of the What-If-Parameter.

Like this you should get your desired result.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[Power_MN]

Hello @selimovd ,

that was helpful! I'm one step closer to my goal.
How can I set the slicer or how can I write the measure, that it only effects one suppliers price?
Background: I want so show, what happens in the ranking, if the price of this particular supplier increase.

Thank you!

Kind regards

Michael

[selimovd]

Hey @Power_MN ,

that's what is described in the link I posted.

You add a What if parameter:

When you press OK a slicer is created from this parameter:

In you Measure you can use the current value from the slicer with the SELECTEDVALUE function.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi