topic Re: Total Count Without Duplicates in Desktop

Total Count Without Duplicates

vishalbaldania — Tue, 25 Jul 2017 10:50:33 GMT

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.

Re: Total Count Without Duplicates

vanessafvg — Tue, 25 Jul 2017 10:53:04 GMT

vishalbaldania unfortunately with this limited information i would need to be a bit psychic to understand the issue :womanvery-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?

Re: Total Count Without Duplicates

vishalbaldania — Tue, 25 Jul 2017 11:01:00 GMT

vanessafvg

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

Formula for Measure: Measure = COUNTROWS(RPT_COMEBACKS)

Re: Total Count Without Duplicates

vanessafvg — Tue, 25 Jul 2017 11:08:40 GMT

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

Re: Total Count Without Duplicates

vishalbaldania — Tue, 25 Jul 2017 11:14:19 GMT

vanessafvg

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

Re: Total Count Without Duplicates

vanessafvg — Tue, 25 Jul 2017 11:15:58 GMT

vishalbaldania counting all rows is simply countrows(tablename)

Re: Total Count Without Duplicates

vishalbaldania — Tue, 25 Jul 2017 11:20:52 GMT

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.

Re: Total Count Without Duplicates

vanessafvg — Tue, 25 Jul 2017 11:26:21 GMT

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?

Re: Total Count Without Duplicates

vishalbaldania — Tue, 25 Jul 2017 11:27:17 GMT

YES that's right

Re: Total Count Without Duplicates

vanessafvg — Tue, 25 Jul 2017 11:34:53 GMT

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

Re: Total Count Without Duplicates

vishalbaldania — Tue, 25 Jul 2017 11:39:06 GMT

vanessafvg

As you said i created Measure A and it's works but I can't see the total rows on the bottom. Might be becasue of i used distictcount if i use something else like count or countrows it does not works

Re: Total Count Without Duplicates

vanessafvg — Tue, 25 Jul 2017 11:44:58 GMT

vishalbaldania ok but can you tell me what you expect to see there? because i dont quite understand the issue here,what is the expected value at the bottom the total is going to return a distinct count of all equipment ids, it look like you only have one equipment id in your table.

i am not quite sure what you objective is, if you explain the business requirement mabye i can help you better

Re: Total Count Without Duplicates

v-jiascu-msft — Tue, 08 Aug 2017 01:40:14 GMT

Hi vishalbaldania,

Could you please mark the proper answer as solution if it's convenient for you? That will be a help to the others.

Best Regards!
Dale