Sum measure with exclusion

[Anonymous]

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

[ryan_mayu · Accepted solution]

@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 · Accepted solution]

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.

[v-cgao-msft · Accepted solution]

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

[Anonymous]

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

[Ashish_Mathur · Accepted solution]

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.

[ryan_mayu · Accepted solution]

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