cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tejapowerbi123
Helper IV
Helper IV

Count based on condition

Hi Community,

Please review below and provide your suggestion,

I want to count organization based on “Decision”, I have used below formula and it not showing right result.

Count =
COUNTX(

    FILTER ( 'Raw Data',' Raw Data '[Decision] = "Award" ),

    ''Raw Data''[organization])

Raw Data

Organization

Decision

ABC

Award

ABC

Award

ABC

Decline

XYZ

Award

 

Final Result

Organization

Decision

Count only based on Awarded

ABC

Award

2

XYZ

Award

1

 

1 ACCEPTED SOLUTION

HI @tejapowerbi123 ,

 

Create a Calculated Column

 

Column = CALCULATE(COUNT(Table2[Decision]),FILTER( ALLEXCEPT(Table2,Table2[Organization]), Table2[Decision] = "Award"))

 

1.jpg2.JPG

 

 

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

 

View solution in original post

13 REPLIES 13
swise001
Continued Contributor
Continued Contributor

Try This:
 
Count = CALCULATE(COUNTROWS('Raw Data'),'Raw Data'[Decision]="Award")

@swise001  I have used your suggestion and still not getting right number.

Count only based on Awarded
1
1

I replicated your scenario and your measure displays the correct result. Below is the pbix file.

@AntrikshSharma thank you so much.I am trying to create in calculated column and i want to use column result to next formula.

If it is a calculated column then you can use this. Modify the IF statement as per your requirement.

Column =
VAR CurrentOrganization = RawData[Organization]
VAR RowsSatisfyingCondition =
    COUNTROWS (
        FILTER (
            RawData,
            RawData[Organization] = CurrentOrganization
                && RawData[Decision] = "Award"
        )
    )
VAR Result =
    IF ( RawData[Decision] = "Award", RowsSatisfyingCondition )
RETURN
    Result

 

@AntrikshSharma sorry still not getting the right result.not right.png

HI @tejapowerbi123 ,

 

Create a Calculated Column

 

Column = CALCULATE(COUNT(Table2[Decision]),FILTER( ALLEXCEPT(Table2,Table2[Organization]), Table2[Decision] = "Award"))

 

1.jpg2.JPG

 

 

Regards,
Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

 

View solution in original post

Thank you so much one for your great suggestions.

You said you are trying to create a calculated column and what you have in the image is a measure.

@AntrikshSharma  no it is column.Thanks

pranit828
Super User II
Super User II

Hi @tejapowerbi123 

 

I would use

 

_Count = 

CALCULATE(COUNTX('Raw Data'[organization]), ALLEXCEPT('Raw Data','Raw Data[Decision]))

(OR)

CALCULATE(COUNTROWS('Raw Data'),'Raw Data'[Decision]="Award")

 

Did I resolve your issue? Mark my post as a solution!

 

Appreciate your Kudos, Press the thumbs up button!!

 

Regards,
Pranit




PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

Hi @tejapowerbi123 

 

I believe what you are looking for is below:

 

CALCULATE (
COUNTROWS('Raw Data'),
FILTER ( ALLEXCEPT('Raw Data','Raw Data'[Decision]),'Raw Data'[Decision]="Award")
)

 

Did I resolve your issue? Mark my post as a solution!

 

Appreciate your Kudos, Press the thumbs up button!!

 

Regards,
Pranit

 




PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile

Thank you so much Pranit, I have used your suggestion but result is still not right.

Count only based on Awarded
2411
2411

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors