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,

 

Have got any solution regarding my questions?

Everything is fine but the issue is in total profit.

 

Thanks,

Arvind.

MFelix's avatar
MFelix
Super 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

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi arvindyadav,

     

    Create the following two measures:

     

    Profict per employee =
IF (
    SUM ( Sheet1[Profit] )
        > CALCULATE ( AVERAGE ( Sheet1[Profit] ); ALL ( Sheet1[AM] ) );
    SUM ( Sheet1[Profit] );
    CALCULATE ( AVERAGE ( Sheet1[Profit] ); ALL ( Sheet1[AM] ) )
)

Total Profit =
IF (
    HASONEFILTER ( Sheet1[AM] );
    [Profict per employee];
    SUMX (
        SUMMARIZE (
            ALL ( Sheet1 );
            Sheet1[Date].[Month];
            Sheet1[AM];
            "ProfitTotal"; [Profict per employee]
        );
        [ProfitTotal]
    )
)

    The use the second one to add to your visual should work, see attached PBIX file.

     

    Regards,

    MFelix

  • arvindyadav's avatar
    arvindyadav
    Post Partisan
    7 years ago

    The above procedure is the same as the previous one so I am still getting the same value as previous.

    How to insert an attachment in the thread. I don't see any option to attach the file. Can you explain to me how I can upload the file?

     

     

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Send a onedrive, googledrive, dropbox, we transfer link in the post.

     

    Regards,

    MFelix

  • 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

  • arvindyadav's avatar
    arvindyadav
    Post Partisan
    7 years ago

    Hi MFelix,

     

    Have you got any solution?

     

    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