Grouping..

[Anonymous]

Hi there,

I am trying to use the 'countdistinct' function within Bi - which seems to work in some capacity, but the totals don't add up which is why I've tried to head down the grouping route. Whenever I group the data together, I lose sight of all remaining columns. 

I 'referenced/copied' my original data set, applied the grouping steps however when merging the 2 tables together my numbers don't add up. 

Example within the below excel data set being used, overall i'm looking to count the number of times a worker has clocked into a building but removing any duplication within the same date. 

Would be keen to get some thoughts - not sure if even heading down the grouping route will help?

I am relatively new to Bi 🙂 

Thanks.

[amitchandak]

@Anonymous Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

distinctcount calculated again for grand total

if you want sum you need to try like

sumx(values(Table[user]), distinctcount(Table[Where]))

User or department is grouping used in visual

Grand total - https://www.youtube.com/watch?v=ufHOOLdi_jk

[Anonymous]

Thanks for the message, I gave your SUM a try but I think the issue is that I am reverting to a date field to support my question.

Original Q:

To count the number of times a worker has clocked into a building, but removing any duplication within the same date. 

Sample Data (within Excel):

Desired Outcome (as illustrated within Excel):

Current view within PowerBi using the 'Grouping Function' along with the 'CountDistinct' against 'User' which gives me the correct result, but the remaining columns are lost (User, Department, Where):

I followed this tutorial to achieve the above for context:

https://www.youtube.com/watch?v=7l4dYEjmgqA 

Is there a way to tie in my original columns sets, so I can apply drill downs as required? 

[Fowmy · Accepted solution]

@Anonymous 

In Power Query, select both the Date and the User Column pressing CTRL, right-click, choose Remove Duplicates. You will get rid of any duplicates within Date and User and retain all other columns as well.

[Anonymous]

This works! Thank you so much!