Need to use If Condition In Power BI Desktop.

[arvindyadav]

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 formula in Power BI Desktop. 

I have a measure of Average Profit/Employee.

Please suggest me how to do =if(Profit > Average Profit/Employee, Profit, Average Profit/Employee) in Power BI Desktop.

Thanks,

Arvind

[MFelix]

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]

Hi @MFelix,

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

Thanks,

Arvind

[MFelix]

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]

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]

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]

Hi @MFelix,

Have got any solution regarding my questions?

Everything is fine but the issue is in total profit.

Thanks,

Arvind.

[MFelix]

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

[arvindyadav]

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]

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

Regards,

MFelix

[arvindyadav]

Hi @MFelix,

Please find the sample file.

https://www.dropbox.com/s/liep5krpxcebyvh/Sample.xlsx?dl=0

Thanks,

Arvind

[MFelix]

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]

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]

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]

Hi @MFelix,

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

Please find attached.

[MFelix]

Hi @arvindyadav,

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

Regards,

MFelix

[arvindyadav]

No

[MFelix]

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

[arvindyadav]

Hi @MFelix,

Have you got any solution?

Thanks,

Arvind

[arvindyadav]

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