IF Statement

[harrinho]

Hi, 

I have the following table with measures from two different sources, however, joins on ID are fine and working.

My issue is that the if statement i have built doesn't return any result. My IF creteria as shown in the screenshot and the code below are:

If Soft Hours = Hard hours, return Hard hours

If Soft Hours = 0, return Hard hours

IF Soft Hours > Hard Hours, return Soft+Hard

If Soft Hours < Hard hours, return Hard hours. 

Both sources contain multiple rows for teh same pmfkey and the hours are rolled up to this key. 

Total Allocations = 
IF(CALCULATE(SUM('CLARITY ALLOCATIONS'[Soft Hours]))=CALCULATE(SUM('CLARITY HARD ALLOC'[Hard_Hrs])),CALCULATE(SUM('CLARITY HARD ALLOC'[Hard_Hrs])),
    IF(CALCULATE(SUM('CLARITY ALLOCATIONS'[Soft Hours]))=0,CALCULATE(SUM('CLARITY HARD ALLOC'[Hard_Hrs])),
        IF(CALCULATE(SUM('CLARITY ALLOCATIONS'[Soft Hours]))>CALCULATE(SUM('CLARITY HARD ALLOC'[Hard_Hrs])),CALCULATE(SUM('CLARITY ALLOCATIONS'[Soft Hours])+SUM('CLARITY HARD ALLOC'[Hard_Hrs])),
            IF(CALCULATE(SUM('CLARITY ALLOCATIONS'[Soft Hours]))<CALCULATE(SUM('CLARITY HARD ALLOC'[Hard_Hrs])),CALCULATE(SUM('CLARITY HARD ALLOC'[Hard_Hrs]))))))

Literally I don't understand why Power BI cannot handle a dead simple sequence of criteria and return random results. The only case it works is when Soft = 0 and returns the Hard hours for ACHSA09. I chose "don't summarize" from the filters pane so as you can see the full rows deployement. 

Is there any other way to achieve this ?

Thanks

[Interkoubess]

Hi @harrinho,

I have a question to better understand your request.

Why 4 conditions because I only see 1 if ( If I am correct):

1. If Soft Hours > Hard Hours, return Soft+Hard else Hard hours

Please check this and let us know.

Ninter

[harrinho]

Indeed, I just deployed it in detail to explain the context better 🙂 

I amended the query as per below 

Total Allocations = IF(CALCULATE(SUM('CLARITY ALLOCATIONS'[Soft Hours]))>CALCULATE(SUM('CLARITY HARD ALLOC'[Hard_Hrs])),CALCULATE(SUM('CLARITY ALLOCATIONS'[Soft Hours])+SUM('CLARITY HARD ALLOC'[Hard_Hrs])),CALCULATE(SUM('CLARITY HARD ALLOC'[Hard_Hrs])))

But I get odd results again 

[Interkoubess]

Hi @harrinho,

Ok fine but I want to understand now your table why do you have for example for PMF Key (ACHS09) 3 times 0 for Soft hours and 3 times 32 for hard hours?

So to sum up for this it will be 0 for soft hours and 96 for hard hours,  correct ?

I will change my formula to something like this while waiting for your inputs...

Total Allocations = var SH= CALCULATE(SUM('CLARITY ALLOCATIONS'[Soft Hours]))
var HH= CALCULATE(SUM('CLARITY HARD ALLOC'[Hard_Hrs])) return IF(SH> HH, SH+HH, HH)

Ninter

[harrinho]

Thanks @Interkoubess. It's because these hours are based on and driven by different projects. Another example below, hopefully shows more clear. 

Your calculation works fine but the problem is that because I summarize this to one line, when it comes to the total row, the calculation is not applied. For example here, I jsut want to roll it up to one line, getting rid of the Projects Column

Then I will have the following 

But in this case I'd like the Total Allocations to return 43+38=81 instead

[v-piga-msft]

Hi @harrinho,

By my tests with the formula from Interkoubess, I cannot reproduce your issue.

If it is convenient, could you share a dummy pbix file which can reproduce the issue, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

Best  Regards,

Cherry