Need to use If Condition In Power BI Desktop.

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

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

Hi MFelix,

Have you got any solution?

Thanks,

Arvind

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

Hi MFelix,

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

Please find attached.

Hi arvindyadav,

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

Regards,

MFelix

No

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