Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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
Hi,
Is this the result you are expecting? You may download my PBI file from here.
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.
Hi,
Is this the result you are expecting? You may download my PBI file from here.
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.
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.
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
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
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?
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
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
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |