Forum Discussion

Power_MN's avatar
Power_MN
Icon for Helper I rankHelper I
5 years ago
Solved

Change Value with Slicer // dynamic Value

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
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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.

5 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    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
     
    • Power_MN's avatar
      Power_MN
      Icon for Helper I rankHelper I
      5 years ago

      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's avatar
        selimovd
        Icon for Most Valuable Professional rankMost Valuable Professional
        5 years ago

        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
         
  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    5 years ago

    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.