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   ...
  • 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

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

     

     

     

     

     

    • kohlivinayak's avatar
      kohlivinayak
      Resolver I
      8 years ago

      Hi MVaughan1

       

      Please share the formula for us to have a look.

       

      Thanks

      • MVaughan1's avatar
        MVaughan1
        Frequent Visitor
        8 years ago

        Hi again kohlivinayak

         

        Rev vs PY (Risk) = IF('Finance data (2)'[vs PY]<[Tolerance Value],1,0)

         

        If I swap [Tolerance Value] for eg "0.1", the values change. 

         

        This is my Tolerance Measure

         

        Tolerance Value = SELECTEDVALUE(Table1[Rev Tolerance])

         

        And below I have shown a picture of the Rev Tolerance table.