Forum Discussion
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 |
Anonymous
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)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.
Hi Anonymous ,
Please try: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 TeamIf 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
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 
ryan_mayuSuper User
Anonymous
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)- Ashish_Mathur
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.
Hi Anonymous ,
Please try: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 TeamIf 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