Forum Discussion

Icon for Helper II rank

Helper 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 instead of the total.

In Excel it would normally be calculated like this

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
Lobs.pbix30 KB

5 Replies

TheoC
Community 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.pbix30 KB
- Lobs
  Helper 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
    Community 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