cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
OneOkCat Frequent Visitor
Frequent Visitor

How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

I have data which looks like this:

ID	numMinutes	dayTime
00b02	7	7/11/2019
00b02	14	7/11/2019
00b02	32	7/10/2019
00b02	18	7/10/2019
00b02	41	7/21/2019
00b02	15	7/21/2019
00b02	5	7/15/2019
00b02	2	7/14/2019
00c59	10	8/29/2019
00c59	4	8/29/2019
00c59	18	7/29/2019
00c59	6	7/31/2019
00c59	13	8/1/2019
00c59	10	9/27/2019
00c59	3	8/2/2019
00c59	2	8/13/2019
00c59	2	7/2/2019
00c59	6	9/28/2019
0121d	3	9/22/2019
0121d	9	8/15/2019
0121d	1	8/15/2019
0121d	16	8/27/2019

I need to find the distinct count of dayTime per ID and sum these distinct counts. Then sum the numMinutes and divide the sum of distinct counts of dayTime to work out the average numMinutes spent per day.

 

I tried using summerize to created a table of counts per ID like follows:

daycount = SUMMARIZE(AccountUsage,AccountUsage[Id],"daycount",DISTINCTCOUNT(AccountUsage[dayTime]))

Then sum the result daycount column as totaldaycount. 

 

This works great when I don't put a date slicer. Once I start changing dates on the slicer, the results become wrong. It seems the Summarize table does not interact with Date Slicer. Is there a way to make this work or make the calculations I desire?

 

Edit: It seems the Summarize method can achieve my calculation. But the sumMinutes needed to be SUM'ed with a FILTER that does a ALLSELECTED(numMinutes),ISONORAFTER(numMinutes),MAX(numMinutes), which @Ashish_Mathur indicated.

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

hi, @OneOkCat 

This looks like a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

 

and for your case, Just try this formula to create a measure

Measure = var _table=SUMMARIZE(AccountUsage,AccountUsage[ID],"_discount",CALCULATE(DISTINCTCOUNT(AccountUsage[dayTime])) ) return
SUMX(_table,[_discount])

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Super User
Super User

Re: How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

Hi,

Is this the result you are expecting?  You may download my PBI file from here.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

11 REPLIES 11
venal Established Member
Established Member

Re: How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

Please refer the below link for your issue to download the PBIX file with the sample data.

 

http://www.mediafire.com/file/5ynjl27hibhk864/OneOkCat-Distinct_Count_by_ID_with_Date_Slicer.pbix

 

If it is not the result, can you share expected result with the sample data.

 

If this post was helpful may I ask you to mark it as solution and give it some kudos?

OneOkCat Frequent Visitor
Frequent Visitor

Re: How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

Hi @venal ,

Thanks for your input.

For the CountMinutes column, I want to have the SUM of the distinct count of the dates. 

ID	CountMinutes	
00b02	3	
00c59	1	
0121d	1	
00b02	2	
00c59	8	
0121d	2	
	17	total

Can you please show me how it's done? Many thanks. In the mean while I'll modify the codes you've provided and see if I can work something out.

 

Note. I tried your measures with my real data. The sumMinutes is correct at 314369 but countMinutes is 188 but should be 13294

Community Support Team
Community Support Team

Re: How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

hi, @OneOkCat 

This looks like a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

 

and for your case, Just try this formula to create a measure

Measure = var _table=SUMMARIZE(AccountUsage,AccountUsage[ID],"_discount",CALCULATE(DISTINCTCOUNT(AccountUsage[dayTime])) ) return
SUMX(_table,[_discount])

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

OneOkCat Frequent Visitor
Frequent Visitor

Re: How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

Hi @v-lili6-msft ,

 

Thanks for your input.

I have done the research and read through many posts regarding sum of measures. All of them don't work with Date Slicers. The Summarized total would stay the same even if you change the dates. I have used the exact formular that you've proposed many days ago to no avail.

Community Support Team
Community Support Team

Re: How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

hi, @OneOkCat 

It should work well, there should be something wrong in other.

Please share your sample pbix file for us have a test.

You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

Hi,

Is this the result you are expecting?  You may download my PBI file from here.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

OneOkCat Frequent Visitor
Frequent Visitor

Re: How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

Hi @Ashish_Mathur ,

Thank you for the reply.

I used your way but as you can see it give the correct individual distinct count per accountId but not the total. I need find the SUM of these distinct counts. The total should be 13294 instead of 90.

Capture.PNG

 

PS Thanks everyone for the contribution so far. I think we are getting close. 

Community Support Team
Community Support Team

Re: How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

hi, @OneOkCat 

It is a measure total problem, not sure why the above formula doesn't work, could you share a simple sample pbix file for us have a test?

 

Best Regards,

Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: How to SUM the DISTINCTCOUNT by ID that works with Date slicer?

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 196 members 1,778 guests
Please welcome our newest community members: