Forum Discussion

arvindyadav's avatar
arvindyadav
Post Partisan
7 years ago

Need to use If Condition In Power BI Desktop.

Hi Team,   I want to establish If condition in power bi which I have made in excel i.e, =if(Profit > Average Profit/Employee , Profit, Average Profit/Employee). So how I can make that type of form...
Need Help
MFelix's avatar
MFelix
Super User
7 years ago

Hi arvindyadav,

 

Since this is a measure the value of it changes accordingly to context, so when making the average Profit/Employee you need to change it filter context.

Without additional information it's hard to give you a correct answer but you need to create something like this:

 

Pofict per employee =
IF (
    SUM ( Table[Profit] )
        > CALCULATE ( [Average Profit/Employee]; ALL ( Table[Employee] ) );
    SUM ( Table[Profit] );
    CALCULATE ( [Average Profit/Employee]; ALL ( Table[Employee] ) )
)

If you can share some sample data and expected result this measure can be made inline with your expectactions, again without any data difficult to respond.

 

Regards,

MFelix

arvindyadav's avatar
arvindyadav
Post Partisan
7 years ago

Hi MFelix,

 

Wow, it works but the total of Average per employee is displaying same as actual profit total. Why So?

 

Thanks,

Arvind

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi arvindyadav,

     

    Again without further information on your data is difficult to get the correct answer.

     

    It depends on the data model, the columns used on your visuals, etc.

     

    Can you share some sample?

     

    Regards,

    MFelix

    • arvindyadav's avatar
      arvindyadav
      Post Partisan
      7 years ago

      Hi MFelix,

       

      I have no permission to share the sample data also but I can send you a screenshot of the data.

      Please find the image

       

      Thanks,

      Arvind

      • MFelix's avatar
        MFelix
        Super User
        7 years ago

        Hi arvindyadav,

         

        Again is a question of context, since the measure is based on the total profit value compared with the average you are getting the total profit and not the sum of the above values you need to do a SUMX statment something like this should work:

         

        IF (
    HASONEVALUE ( Table[Employee] );
    IF (
        SUM ( Table[Profit] )
            > CALCULATE ( [Average Profit/Employee]; ALL ( Table[Employee] ) );
        SUM ( Table[Profit] );
        CALCULATE ( [Average Profit/Employee]; ALL ( Table[Employee] ) )
    );
    SUMX (
        Table;
        IF (
            SUM ( Table[Profit] )
                > CALCULATE ( [Average Profit/Employee]; ALL ( Table[Employee] ) );
            SUM ( Table[Profit] );
            CALCULATE ( [Average Profit/Employee]; ALL ( Table[Employee] ) )
        )
    )
)

        Regards,

        MFelix