cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Sum measure with exclusion

Dear Gurus,

Appreciate your help in calculating the below sum 35 minus hours for any month that was flagged 1 at least once (-7 for January and -10 for April. So result should be 35-7-10 = 18

 Name Month Hours Flag Zackary Orr January 4 1 Zackary Orr January 2 0 Zackary Orr January 1 0 Zackary Orr February 8 0 Zackary Orr March 2 0 Faye Daniels April 5 1 Faye Daniels April 5 0 Faye Daniels June 4 0 Faye Daniels July 4 0 Total 35
3 ACCEPTED SOLUTIONS
Super User

is ths what you want?

1. create an index column in PQ

_flag = if(CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Name],'Table'[Month]))>1&&'Table'[Index]=CALCULATE(min('Table'[Index]),ALLEXCEPT('Table','Table'[Name],'Table'[Month])),1,0)

_flag2 = if(CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Name],'Table'[Month]))>1,1)

Proud to be a Super User!

Super User

Hi

Enter this calculated column formula

``Flag2 = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Name]=EARLIER(Data[Name])&&Data[Month]=EARLIER(Data[Month])&&Data[Flag]=1))>0,1,BLANK())``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Hi @sabry_303 ,

``````Hours 2 =
IF (
HASONEVALUE ( 'Table'[Name] ),
[Hours],
SUMX (
FILTER (
'Table',
NOT 'Table'[Month]
IN CALCULATETABLE (
VALUES ( 'Table'[Month] ),
'Table'[Flag] = 1,
ALLEXCEPT ( 'Table', 'Table'[Name] )
)
),
[Hours]
)
)``````

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

4 REPLIES 4
Community Support

Hi @sabry_303 ,

``````Hours 2 =
IF (
HASONEVALUE ( 'Table'[Name] ),
[Hours],
SUMX (
FILTER (
'Table',
NOT 'Table'[Month]
IN CALCULATETABLE (
VALUES ( 'Table'[Month] ),
'Table'[Flag] = 1,
ALLEXCEPT ( 'Table', 'Table'[Name] )
)
),
[Hours]
)
)``````

Best Regards,
Gao

Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

Frequent Visitor

In other words, I would like to flag any person who was at least flagged once in a certain month

 Name Month Hours Flag Flag2 Zackary Orr January 4 1 1 Zackary Orr January 2 0 1 Zackary Orr January 1 0 1 Zackary Orr February 8 0 Zackary Orr March 2 0 Faye Daniels April 5 1 1 Faye Daniels April 5 0 1 Faye Daniels June 4 0 Faye Daniels July 4 0 Total 35
Super User

Hi

Enter this calculated column formula

``Flag2 = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Name]=EARLIER(Data[Name])&&Data[Month]=EARLIER(Data[Month])&&Data[Flag]=1))>0,1,BLANK())``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

is ths what you want?

1. create an index column in PQ

_flag = if(CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Name],'Table'[Month]))>1&&'Table'[Index]=CALCULATE(min('Table'[Index]),ALLEXCEPT('Table','Table'[Name],'Table'[Month])),1,0)

_flag2 = if(CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Name],'Table'[Month]))>1,1)

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.