cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Count occurrence with condition

Hi 

I have a table looks like this:

Capture2.PNG

What I want to achieve is to count the occurrence of the client no. where the Position is 10.

 

I want to use Card visualization in my report: 

1. have a total buying customer (the distinct count of client no) 20

2. 16 of them are buying for the first time ( the distinct count of client no =1 )

3. 4 of them are returning (the distinct count of client no >1)

 

The report should have three Cards:

22222.JPG

Please help 😞

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Count occurrence with condition

Hello @Flynn0327 

We need just a few measures to get what you are looking for.

A count of all the rows, this we will use for filtering later.

Line Count = COUNTROWS ( 'Table' )

A count of the distinct customers (the 20)

Buying Customers = DISTINCTCOUNT ( 'Table'[Client no] )

The first time buyers

First Time Buyers = 
CALCULATE ( 
    [Buying Customers], FILTER ( VALUES ( 'Table'[Client no] ) , [Line Count] = 1 )
)

And the returning customers

Returning Customers = 
CALCULATE ( 
    [Buying Customers], FILTER ( VALUES ( 'Table'[Client no] ) , [Line Count] > 1 )
)

I'm not sure what you mean about the filter on position 10 but the position filed can be added as a page level filter and that will limit all the measures above to only calculate against the lines in position 10 if that is what you are looking for.

I have attached my sample file for you to look at.

View solution in original post

2 REPLIES 2
Highlighted
Super User IV
Super User IV

Re: Count occurrence with condition

Hello @Flynn0327 

We need just a few measures to get what you are looking for.

A count of all the rows, this we will use for filtering later.

Line Count = COUNTROWS ( 'Table' )

A count of the distinct customers (the 20)

Buying Customers = DISTINCTCOUNT ( 'Table'[Client no] )

The first time buyers

First Time Buyers = 
CALCULATE ( 
    [Buying Customers], FILTER ( VALUES ( 'Table'[Client no] ) , [Line Count] = 1 )
)

And the returning customers

Returning Customers = 
CALCULATE ( 
    [Buying Customers], FILTER ( VALUES ( 'Table'[Client no] ) , [Line Count] > 1 )
)

I'm not sure what you mean about the filter on position 10 but the position filed can be added as a page level filter and that will limit all the measures above to only calculate against the lines in position 10 if that is what you are looking for.

I have attached my sample file for you to look at.

View solution in original post

Highlighted
Helper I
Helper I

Re: Count occurrence with condition

Hi @jdbuchanan71  

Thank you very much for your help! 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Come join us today! Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors