Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

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

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!

1 ACCEPTED SOLUTION
Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
10 REPLIES 10
Helper I

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

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 ?

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Helper I

@Ashish_Mathur is a calculated column not a measure.

Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Helper I
Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Helper I

Thank you very much, appreciate your help !

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User

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?

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Community Support

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.

## Helpful resources

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors