Sum of a calculated column which excludes the lines for which a criteria is met

[Bratone]

Hi all,

I want to create a calculated column which will return a sum of values, excluding the lines for which a criteria is met, see below example. So if the status is "Idle" I want to have the sum of all the others statuses for the particular person on each day, if not, to return the same hours.

I highlighted with red the expected behaviour, I tried this formula but I get a blank:

Column1 = IF(Status[L1 Status]="Idle",CALCULATE(SUM(Status[Status Hours]),Status[L1 Status]<>"Idle"),Status[Status Hours])

Any idea on how can I fix this please ? 

Thank you!

[Bratone]

@Ashish_Mathur 

I am looking for a calculated column and yes the total for 9th of Jan should be 6.14

@v-zhangti 

That's exactly what I am looking for and even though I used the same formula, for me it's not working:

Test_Status_Hours = IF(AHP_Utilization[L1 Status]="Idle",
    CALCULATE(SUM(AHP_Utilization[Status Hours]),
        FILTER(AHP_Utilization,
                [L1 Status]<>"Idle"
                    && [Select Data Part] = EARLIER([Select Data Part])
        )
    ),
    AHP_Utilization[Status Hours]
)

Any idea why ?

[Ashish_Mathur]

The formula suggested by @v-zhangti should be written as a calculated column formula (not as a measure).  Make that correction and you will get your desired result.

[Bratone]

@Ashish_Mathur is a calculated column not a measure.

[Ashish_Mathur]

Share the download link of the PBI file with the calculated column formula already written there.

[Bratone]

@Ashish_Mathur link below:

https://we.tl/t-IwsRFHdcnT

[Ashish_Mathur · Accepted solution]

Hi,

Revise the "Test_Idle_Hours" calculated column formula to

Test_Idle_Hours = 
IF ( [L1 Status] = "Idle",
    CALCULATE ( SUM ( 'AHP_Utilization'[Status Hours] ),
        FILTER ('AHP_Utilization',
                [L1 Status] <> "Idle"
                    && [Select Data Part] = EARLIER ('AHP_Utilization'[Select Data Part] )
        &&AHP_Utilization[Login Name]=EARLIER(AHP_Utilization[Login Name]))
    ),
    AHP_Utilization[Status Hours]
)

 This is the result you will get

[Bratone]

Thank you very much, appreciate your help !

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

Are you looking at a calculated column formula solution or a measure solution?  Also, what should the total at the day level be?  So for Jan 9, 2023, should the answer be 6.14?

[v-zhangti]

Hi, @Bratone 

You can try the following methods.

Column = 
IF ( [L1 Status] = "Idle",
     CALCULATE ( SUM ( 'Status'[Status Hours] ),
        FILTER ('Status',
            [L1 Status] <> "Idle"
                && [Date] = EARLIER ( 'Status'[Date] )
        )
    ),
    [Status Hours]
)

Result:

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.