Forum Discussion

Lobs's avatar
Lobs
Icon for Helper II rankHelper II
4 years ago
Solved

%IMpact

Hi,    Im trying to get the impact of my survey mix to the percent to goal total using a measure.    But i always end up hitting the survey mix on the same row/line of the current PTG value...
Need Help
  • TheoC's avatar
    TheoC
    4 years ago

    Hi Lobs 

     

    To get the "cell locked" like you would in Excel for the total (i.e. if total was in A1 you would use $A$1), you need to tell Power BI to ignore all filters in your measure.  You can use the below:

     

    _mImpact = 

    VAR _1 = SUM ('Table'[PTG NPS] ) 
    VAR _2 = CALCULATE ( SUM ('Table'[% Survey Mix] ) , ALL ('Table' ) )

    VAR _3 = DIVIDE ( _1 , _2 )

    RETURN

    _3
    Output will be like below:
     

     

    I've attached a PBIX for further assistance as well.

    Thanks,

    Theo

TheoC's avatar
TheoC
Icon for Community Champion rankCommunity Champion
4 years ago

Hi Lobs 

 

To get the "cell locked" like you would in Excel for the total (i.e. if total was in A1 you would use $A$1), you need to tell Power BI to ignore all filters in your measure.  You can use the below:

 

_mImpact = 

VAR _1 = SUM ('Table'[PTG NPS] ) 
VAR _2 = CALCULATE ( SUM ('Table'[% Survey Mix] ) , ALL ('Table' ) )

VAR _3 = DIVIDE ( _1 , _2 )

RETURN

_3
Output will be like below:
 

 

I've attached a PBIX for further assistance as well.

Thanks,

Theo

  • Lobs's avatar
    Lobs
    Icon for Helper II rankHelper II
    4 years ago

    Thank you, ill give this a try, however my SurveyMix and PTG NPS are measures would the approach be the same?

    • TheoC's avatar
      TheoC
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Lobs yeah, should be the same logic just change the SUM (...) to the [measure]. Everything else should be good. If it doesn't work, I'll send through solution when I'm in front of computer next.

       

      Cheers,

      Theo

      • Lobs's avatar
        Lobs
        Icon for Helper II rankHelper II
        4 years ago

        Finally, i can sleep 🙂 the ignoring filters paved the way, appreciate it