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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
Bratone
Helper I
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.

Bratone_0-1674163154956.png

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

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

Untitled.png


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

View solution in original post

10 REPLIES 10
Bratone
Helper I
Helper I

@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:

 

Bratone_1-1674197455758.png

 

 

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]
)

 

Bratone_0-1674197414218.png

 

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.


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

@Ashish_Mathur is a calculated column not a measure.

 

 

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/

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

Untitled.png


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

Thank you very much, appreciate your help !

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
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/
v-zhangti
Community Support
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]
)

vzhangti_0-1674177915715.png

Result:

vzhangti_1-1674177985679.png

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
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.