Distinct count figures for Min and Max show up different

[samioberoi]

Hi
I tried to calculate Min and max to find out the employees' first access card scan time into the buildings and the last exit access card scan time and did the distinct count of those for all the employees across 4 different sites, but the total figures seem to be coming up wrong. e.g. if in Bothe site total count of card numbers is showing 1195 so the Min which is the entry time into the building for all the employees should be same and the exit time also should be almost the same. So, why is it showing the Min count, which i put as distinct count, as 380 and the Max distinct count is showing 419 and total below for all the sites is also showing wrong as 650 & 726?

Will appreciate the help.
Thanks

[amitchandak]

@samioberoi , What you need in grand total say sum

Sumx(Values(Table[Customer name]), Calculate(Min(Table[Value])) )

or

Maxx(Values(Table[Customer name]), Calculate(Min(Table[Value])) )

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[samioberoi]

Hi Amit, 

Thanks for your reply. The Min and max functions i created as --"Min(Column Name),filter(allexcept(tablename,customer number), column IN&Out = "IN")) --work fine for each individual, but when i put those in fields and choose count Distinct then the total figures don't seem to match with total of card numbers nor with the total of all locations like Bothwell, Hillington etc together. 

I tried to use your calculation as well like "Sumx(Values(Table[Customer name]), Calculate(Min(Table[Value])) )". It gives one Min time for each individual , but doesn't give the Min timing for each date when some employees would have come in at different dates as well & the calculation "Maxx(Values(Table[Customer name]), Calculate(Min(Table[Value])) )" doesn't seem to work. 
I just want to figure out why the totals are coming up less or different when put in the table.

Thanks

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[samioberoi]

PBI file 

Hi , 

Please find my demo data file attached.

Thanks

Sam

[Ashish_Mathur]

That is a dead link.

[samioberoi]

Hi Ashish,

It may not work for you again. Sorry about that. Is there a way to create the link without the workspace? I only have got the PBI desktop. 

Thanks

[samioberoi]

"C:\Users\Medacy1\OneDrive\Desktop\Sean's Data Dummy.xlsx 2.pbix""C:\Users\Medacy1\OneDrive\Desktop\Sean's Data Dummy.xlsx 2.pbix" 

Hi Ashish,

Sorry for the last one which didn't work. Can you check this link please? Hope this one works fine and i really need some help on this please. 
Appreciate your effort in trying to help me.

Regards

Sam

[Ashish_Mathur]

That link does not work either.

[samioberoi]

Hi Ashish

I am so sorry. I don't have PBI service so that i can create and share a link with you. I just have PBI desktop. Is there any other way that i can share the desktop version with you?

Appreciate your help and timely response.

Regards

Sam

[Ashish_Mathur]

Upload the PBI file to Google Drive and share the download link.

[samioberoi]

https://drive.google.com/file/d/1Lyd9xf5tQtIV-LXqCMj-wektW0nCtURQ/view?usp=sharing

Hi Ashish, 
Please find the dummy data file attached. Hope it works this time. 

I just need to count first entry time into the building and the last exit time from the building for each employee per day, which seems to be fine as per the calculations i used by taking Min and Max out. Then, i needed to count those First In's and Last Out's for each employee per day for each site and in total for all those sites as well, which i tried in this dummy data file in the form of Clusters as an example e.g i represented Site 1 as Cluster 1, site 2 as Cluster 2 etc. When i use the filter for employees and i choose one employee who would have used the access card on 21st Dec at 8.30AM at the first entry barrier and would have used the access card last time to exit after finishing for the day at 5.30 and on the next day on 22nd Dec would have used the access card again to enter into the building at the same time 8.30 AM and would have left for the day at 5.31 PM, it counts those 8.30AM entry timings on two different dates as only because i am using distinct count. 

I would like to use this distinct count for each day only, so it should count 8.30 AM entry timings for two different dates for each employee as 2 and not as only 1.

The calculations i used are as following:

For Calculating Min --- 

Min = CALCULATE(

        MIN(Sheet1[Time - Copy]),

        FILTER(ALLEXCEPT(Sheet1,

        Sheet1[Card Number]),Sheet1[In&Out] = "(IN)"))

For Max ---

Max= CALCULATE(

        MAX(Sheet1[Time - Copy]),

        FILTER(ALLEXCEPT(Sheet1,

        Sheet1[Card Number]),Sheet1[In&Out] = "(OUT)"))

Then to calculate count of these Min and Max i used:

Distinct Count Min = SUMX(values(Sheet1[Card Number]),CALCULATE(DISTINCTCOUNT(Sheet1[Min])))

Distinct Count Max = SUMX(values(Sheet1[Card Number]),CALCULATE(DISTINCTCOUNT(Sheet1[Max])))

Please help me with this problem and i am already so thankful for your effort to help.

Regards

[Ashish_Mathur]

There is again an Access Denied message.  Create an MS Excel file with dummy data (which is representative of your data on the PBI file) and show the expected reult there is a Table format.