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
arvindyadav's avatar
arvindyadav
Post Partisan
7 years ago

Hi MFelix,

 

Now the total issue fixed but the average is not correct. There are 17 AM's and the total profit is 279384.88.

Then The average is 279384.88/17=16434.40. But in your case the average display as 173.85 which is not correct.

 

Please find the attached.

 

 

Thanks,

Arvind

MFelix's avatar
MFelix
Super User
7 years ago

Hi arvindyadav,

 

Replace the first measure by:

 

Profict per employee =
IF (
    SUM ( Sheet1[Profit] ) = BLANK ();
    BLANK ();
    IF (
        SUM ( Sheet1[Profit] )
            > AVERAGEX (
                SUMMARIZE (
                    ALL ( Sheet1 );
                    Sheet1[Date].[Month];
                    Sheet1[AM];
                    "ProfitTotal"; SUM ( Sheet1[Profit] )
                );
                [ProfitTotal]
            );
        SUM ( Sheet1[Profit] );
        AVERAGEX (
            SUMMARIZE (
                ALL ( Sheet1 );
                Sheet1[Date].[Month];
                Sheet1[AM];
                "ProfitTotal"; SUM ( Sheet1[Profit] )
            );
            [ProfitTotal]
        )
    )
)

 

See attach the correct PBIX file.

 

Regards,

MFelix

  • arvindyadav's avatar
    arvindyadav
    Post Partisan
    7 years ago

    Hi MFelix,

     

    Not works. For every month and individual month, the total is the same.

     

    Please find attached.

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi arvindyadav,

     

    Is you tabel setup as the one you gave me on the example file?

     


    Regards,

    MFelix

  • arvindyadav's avatar
    arvindyadav
    Post Partisan
    7 years ago

    No

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi arvindyadav,

     

    As I refer this calculation are based on context so if your model setup is different then the final result will not match what you need.

     

    Can you share a mockup of your model so I can make the right adjustment to the formulas?

     

    If you want you can share it trhough private message to avoid publishing sensitive data.

     

    Regards,

    MFelix