Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

hi, @Anonymous 

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

Ashish_Mathur
Super User
Super User

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
Anonymous
Not applicable

My apologies. 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.

 

The Issue is now resolved.

Ashish_Mathur
Super User
Super User

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/
Anonymous
Not applicable

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. 

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/
Anonymous
Not applicable

Hi @Ashish_Mathur @v-lili6-msft ,

Thanks a lot for the help.

Here is the Excel file containing the data that I am using. 

http://www.mediafire.com/file/4fomt7u4vowaw9v/sampleData.xlsx/file

I want it to work with a date slicer like this

Capture.PNG

hi, @Anonymous 

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.
venal
Memorable Member
Memorable Member

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?

Anonymous
Not applicable

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

hi, @Anonymous 

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.
Anonymous
Not applicable

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.

hi, @Anonymous 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.