Forum Discussion

BaldAccountant's avatar
BaldAccountant
Helper III
8 months ago
Solved

Power Pivot Formula Bogging down Excel

This is a power pivot question, not Power BI

I am trying to determine if I have a favorable or unfavorable variance with a dynamic formula and it is locking up excel.   I am not sure if it is my formula or if I am just trying to do too much in Power Pivot/Excel

 

Here is the first formula I tried :

Test P:=SWITCH(TRUE(),

[Selected good bad]="Good"&&[Test O]>=0,"Good",

[Selected good bad]="Good"&&[Test O]<0,"Bad",

[Selected good bad]="Bad"&&[Test O]<=0,"Good",

[Selected good bad]="Bad"&&[Test O]>0,"Bad",0)

 

Here is  the second one I tried:

Test Q:=SWITCH([Selected good bad],

"Good",IF([Test O]>=0,"Good","Bad"),

"Bad",IF([Test O]<=0,"Good","Bad"),0)

Please let me know if I am doing something wrong in my formulas.  Test O is a formula that calculates the difference between the filtered formula and the average for all doctors.  So then if it is a "Good: Stat, that means that a positive difference from the average would be favorable (i.e. More cases than average.   A "Bad" calculation is where a positive differnce would be unfavorable, such as a higher cost per case.  

Sorry I can't include my file because it contains HIPPA info.

  • amitchandak's avatar
    amitchandak
    8 months ago

     

    BaldAccountant , I think it's a problem 
    (!_IsGood && _Diff <= 0)

    Try, if not can work(Power BI we use not )
    (not _IsGood && _Diff <= 0)

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    8 months ago

    BaldAccountant , Try if this can help

     

    Test Result :=
VAR _IsGood = [Selected good bad] = "Good"
VAR _Diff   = [Test O]
RETURN
IF(
    (_IsGood  && _Diff >= 0) ||
    (!_IsGood && _Diff <= 0),
    "Good",
    "Bad"
)

     

    • BaldAccountant's avatar
      BaldAccountant
      Helper III
      8 months ago

      I am getting this error

      I am not sure why it is showing the extra two ")"  I just copied and pasted in your solution

       

      • amitchandak's avatar
        amitchandak
        Super User
        8 months ago

         

        BaldAccountant , I think it's a problem 
        (!_IsGood && _Diff <= 0)

        Try, if not can work(Power BI we use not )
        (not _IsGood && _Diff <= 0)

    • BaldAccountant's avatar
      BaldAccountant
      Helper III
      8 months ago

      For some reason if I use a second variable for the "Bad" instead of ! _IsGood then it works.

      Test Result:=VAR _IsGood = [Selected good bad] = "Good"

      Var isbad = [Selected good bad]="Bad"

      VAR _Diff = [Test O]

      RETURN

      IF(

      (_IsGood && _Diff >= 0) ||

      (isbad && _Diff <= 0),

      "Good",

      "Bad"

      )

       

      I am not sure what to do as far as marking a solution?  I will wait and if you can tell me what I did wrong using your solution I will mark yours as the solution.  I want you to get credit for tellin me to use variables.