Forum Discussion

AndrejZitnay's avatar
AndrejZitnay
Post Patron
9 years ago
Solved

Calculation issue due to interaction

 

Hello Power BI experts.

 

I have little issue with one measurement.

 

Rate = [Measure Renewals related]/[Settlements]

 

Is saying that 1624 / 218 is 26.36%

It should be actually 13.42%

 

I have there some interaction conditions and calculation is

picking up 827 / 218 is 26.36%

 

I have two date filters on numerator and denominator and in one occasion is interaction blocked as it must be that way.

 

Is there measurement for actual % so measurement will ignore interaction?

 

Thank you.

 

Kind regards.

 

Andrej

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi AndrejZitnay,

     

    Thanks for the detailed explanation. Now I understand it totally. And I believe it you can use ALL or ALLExcept function to get the expected result in this scenario. However, without the sample data for testing, it's hard to provide a tested and correct formula for you. So could you try the formula below to see if it works? :smileyhappy:

    Rate =
[Measure Renewals related]
    / CALCULATE ( [Settlements], ALL ( 'tab B'[DateColumn] ) )

    Regards

12 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi AndrejZitnay,

    I have two date filters on numerator and denominator and in one occasion is interaction blocked as it must be that way.

     

    Is there measurement for actual % so measurement will ignore interaction?

    If I understand you correctly, you should be able to use ALL function which can ignore any filters that might have been applied in your scenario. The formula below is for your reference. :smileyhappy:

    Rate = CALCULATE ( [Measure Renewals related], ALL ( Table[DateColumn] ) ) / [Settlements]

    Note: you need to replace Table[DateColumn] with the real table and column name that you're using as Slicers on your report.

     

    Regards

    • AndrejZitnay's avatar
      AndrejZitnay
      Post Patron
      8 years ago

      Hello v-ljerr-msft 

       

      Thank you for your respond.

       

      I will try to make it more clear.

       

      I have two date slicer on my measure.

       

      1st Date of renewals which have all active interaction

      2nd Retention Renwals which have one blocekd interaction as settlements shouldn't change.

       

      Both Measures for Renewals & Settlements comes from excel tab A (symple count if formula)

       

      Slicer Date of Renewals is Date table for excel tab A & excel tab B

      Slicer Retention Periods is date from excel tab B

       

       

      Many thanks in advance for your kind help.

       

      Kind regards.

       

      Andrej

       

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        8 years ago

        Hi AndrejZitnay,

         

        Thanks for the detailed explanation. Now I understand it totally. And I believe it you can use ALL or ALLExcept function to get the expected result in this scenario. However, without the sample data for testing, it's hard to provide a tested and correct formula for you. So could you try the formula below to see if it works? :smileyhappy:

        Rate =
[Measure Renewals related]
    / CALCULATE ( [Settlements], ALL ( 'tab B'[DateColumn] ) )

        Regards

    • AndrejZitnay's avatar
      AndrejZitnay
      Post Patron
      9 years ago

      Here it is. I need to get 13.42% out of measurment between Related Renewals & Settlements

       

       

      • SivaMani's avatar
        SivaMani
        Resident Rockstar
        9 years ago

        How did you calculate Retention Rate?

         

        I mean the formula if you have used DAX