Forum Discussion

arvindyadav's avatar
arvindyadav
Icon for Post Partisan rankPost 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
Icon for Super User rankSuper 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
Icon for Post Partisan rankPost 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
    Icon for Super User rankSuper 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

     

    • arvindyadav's avatar
      arvindyadav
      Icon for Post Partisan rankPost Partisan
      7 years ago

      Hi MFelix,

       

      Still not getting the correct value if I adding all value which was I sent in the screenshot that addition is 

      $101,966.43

       but here I am getting $81123395.61 which is very huge.

      Thanks,

      Arvind

    • arvindyadav's avatar
      arvindyadav
      Icon for Post Partisan rankPost Partisan
      7 years ago

      Hi MFelix,

       

      Have got any solution regarding my questions?

      Everything is fine but the issue is in total profit.

       

      Thanks,

      Arvind.

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        7 years ago

        HI arvindyadav,

         

        Although you have an image is not clear on the way the tables and the fields in you matrix relate to each other try to do the following.

         

        Split the measure in two:

         

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



Total Profit =
IF (
    HASONEVALUE ( Table[Employee] );
    [Profit/Average];
    SUMX ( Table; [Profit/Average] )
)

        Then use the last measure on your table.

         

        If you cannot do it can you share a sample of you data maybe through private message so I can check the calculations and setup of the model?

         

        Regards,

        MFelix