Skip to main content
cancel
Showing results for 
Search instead 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

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

 

NameMonthHoursFlag

Zackary Orr

January41
Zackary OrrJanuary20
Zackary OrrJanuary10
Zackary OrrFebruary80
Zackary OrrMarch20

Faye Daniels

April51

Faye Daniels

April50
Faye DanielsJune40
Faye DanielsJuly40
    
 Total35 
3 ACCEPTED SOLUTIONS

@sabry_303 

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)
 
11.PNG
 
 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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.

Ashish_Mathur_0-1706584570796.png

 


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

View solution in original post

v-cgao-msft
Community Support
Community Support

Hi @sabry_303 ,

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

vcgaomsft_0-1706595057501.png

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

View solution in original post

4 REPLIES 4
v-cgao-msft
Community Support
Community Support

Hi @sabry_303 ,

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

vcgaomsft_0-1706595057501.png

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

sabry_303
Frequent Visitor

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

 

NameMonthHoursFlagFlag2

Zackary Orr

January411
Zackary OrrJanuary201
Zackary OrrJanuary101
Zackary OrrFebruary80 
Zackary OrrMarch20 

Faye Daniels

April511

Faye Daniels

April501
Faye DanielsJune40 
Faye DanielsJuly40 
     
 Total35  

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.

Ashish_Mathur_0-1706584570796.png

 


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

@sabry_303 

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)
 
11.PNG
 
 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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