Forum Discussion

MVaughan1's avatar
MVaughan1
Frequent Visitor
8 years ago
Solved

Dynamically changing assumptionss from Report view

Hello, I am trying to find a way of displaying assumptions that can be changed in the Report view.

 

I cannot share my data for confidentiality, but here is a representative sample of the data

 

 ABCD E
 

Product 

Name

Actual RevenueForecast RevenueLast Year RevenueIs Actual Revenue(B) within 10% (Tolerance) of Forecast Revenue (C) and Last Year Revenue (D)
1X9001000800No
2Y700750700Yes
      
 Tolerance10%   

 

My ideal scenario is to have a Tolerance level filter displayed on the report which can then be altered dynamically by the end user.  So in my table above, the ability to select for example 15% which would then alter column E results )

 

Any suggestions very gratefully received.

 

Thanks,  Mark

  • MVaughan1's avatar
    MVaughan1
    8 years ago

    Hi kohlivinayak,

     

    I have got this to work, not because of the decimal but because my formula was in a calculated column rather than a measure.  

     

    The Selected Value and IF HAS ONE VALUE functions both seem to rely on measures.   Not sure if this is basic Power BI chapter 1 that I skipped over but certainly a key learning point for me.       What is a bit of a pain is that it seems as though I will have to convert all my existing columns into measures.  I got this to work by changing my vs PY to a "SUM vs PY" measure..     I don't really understand why this is needed, but I shouldn't take any more of your time on this.

     

    Thanks once again,  really appreciate your help.

     

    Mark

7 Replies

  • kohlivinayak's avatar
    kohlivinayak
    Resolver I
    8 years ago

    Hi MVaughan1

     

    I am hoping you already have the formula for the calculation and you are struck with the dynamic tolerance.

     

    Create a new table (in excel or in power bi) having one column with values from 1 to 100 (or values you want your user to filter the tolerance on)

     

    Use this coumn on the filter visual on the report (single selection only enabled).

     

    create a measure as below

    Tolerance value = selectedvalue(tolerancetable[column])

     

    And in the formula use this measure in place of tolerance percentage.

     

    Thanks

    Vinayak

     

     

    • MVaughan1's avatar
      MVaughan1
      Frequent Visitor
      8 years ago

      HI kohlivinayak

       

      First of all thank you very much for this - I really appreciate this guidance.   

       

      I think I'm nearly there, but I appear to have a glitch which I wonder if you could help me on.

       

      I have followed your instructions which were great, but I can't seem to get the Tolerance Value to change my column.   I have tested my formula and it works (so I can replace the Tolerance Value with eg "0.1" and it works).    The Tolerance Value appears to work because my table refreshes every time I change the Rev Tolerance (Filter)Value, but the results are always the same ie it shows a 1 for any negative figure in the "vs PY" column. 

       

      Do you have any further ideas on this please ?

       

      Mark

       

      With the selection I would expect a "1" to appear in each of the rows in column "Rev vs PY (Risk)" apart from row 4 and 5

       

       

       

       

       

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    8 years ago

    Hi MVaughan1,

     

    "Is Actual Revenue(B) within 10% (Tolerance) of Forecast Revenue (C) and Last Year Revenue (D)"

    <--- Could you clarify more details about your logic and expected result?

     

    Regards,

    Jimmy Tao