cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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[Status Hours]
)``````

This is the result you will get

Regards,
Ashish Mathur
http://www.ashishmathur.com
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
Helper I

@Ashish_Mathur is a calculated column not a measure.

Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com
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[Status Hours]
)``````

This is the result you will get

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Thank you very much, appreciate your help !

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
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
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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors