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

Total Count Without Duplicates

@vanessafvg 

 

How to Count total number of rows in table after using some filter. I have created column and Measure to calculate number of rows in table but could not get right result.

 

As you can see in pic 4th rows (Yellow Highlighted) has count value 2 insted 1. Total count of table should be 5 insted 6..  Please guide me how can i calculate the right count.BI.PNG

 

 

12 REPLIES 12
Highlighted
Super User II
Super User II

Re: Total Count Without Duplicates

@vishalbaldania unfortunately with this limited information i would need to be a bit psychic to understand the issue Woman Very Happy

 

as i said i need a screen shot of your relationships if you are using more than one table?

can you also post how you did your measures, the code?





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

Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Total Count Without Duplicates

@vanessafvg 

 

Formula for Column: Column = COUNT(RPT_COMEBACKS[Equipment ID])

 

Formula for Measure: Measure = COUNTROWS(RPT_COMEBACKS)BI1.PNG

 

 

BI2.PNG

 

 

Highlighted
Super User II
Super User II

Re: Total Count Without Duplicates

@vishalbaldania so are you wanting a unique count  of equipment id, or just a count of all the rows in the table?

 

Formula for Column: Column = distinctCOUNT(RPT_COMEBACKS[Equipment ID])

 

Formula for Measure: Measure = distinctCOUNT(RPT_COMEBACKS)

 

this is for unique count





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

Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Total Count Without Duplicates

@vanessafvg

 

Could you please tell me formula for count of all rows in the table. becasue i want to use that as KPI

Highlighted
Super User II
Super User II

Re: Total Count Without Duplicates

@vishalbaldania counting all rows is simply countrows(tablename)





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

Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Total Count Without Duplicates

@vanessafvg

 

As you said i used DISTINCTCOUNT(RPT_COMEBACKS[Equipment ID]) but the still output does not right (you can see in image). if you calculate total number of rows in table then it would be 9 insted 10.

 

I also used COUNTROW: Measure = COUNTROWS(RPT_COMEBACKS) but result is not changed. BI1.PNG

 

 

BI2.PNG

 

 

Highlighted
Super User II
Super User II

Re: Total Count Without Duplicates

@vishalbaldania  why are you creating a column?  they should both be measures

 

just so that i understand what you trying to do.

 

you want to see the total rows in the table vs the count of unique equipment id's?

 

 





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

Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Total Count Without Duplicates

YES that's right

Highlighted
Super User II
Super User II

Re: Total Count Without Duplicates

@vishalbaldania change the column to measure and see if it adjusts it properly

 

ps are you wanting to see the total of all on every row, or only what is related to that row

 

you also need to check your data because the measure might be correct, if you still struggling you can send me your pbix

 

if i am not understanding you 

detail what you expect to see





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

Proud to be a Super User!




Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

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