Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Solved! Go to 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
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 ?
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.
Share the download link of the PBI file with the calculated column formula already written there.
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
Thank you very much, appreciate your help !
You are welcome.
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?
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.
User | Count |
---|---|
120 | |
65 | |
62 | |
56 | |
50 |
User | Count |
---|---|
177 | |
85 | |
70 | |
63 | |
55 |