Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I would like to calculate the % of Id's in the table underneath that occures before Hour = 15 and after within each date.
In the example table underneath the Id 11111111 on date 24.05.2018 hour = 10 also have an occurence at 16 so the result of 24.05.2018 will therefore be 1/6 = 17%.
Date Id Hour UniqueId Count
24.05.2018 | 11111111 | 10 | 111111111 | 1 |
25.05.2018 | 11111111 | 11 | 11111111233 | 1 |
24.05.2018 | 22222222 | 11 | 222222221 | 1 |
24.05.2018 | 33333333 | 11 | 333333331 | 1 |
24.05.2018 | 44444444 | 12 | 444444441 | 1 |
24.05.2018 | 11111111 | 16 | 111111113 | 1 |
30.06.2018 | 11111111 | 10 | 1111111110 | 1 |
30.06.2018 | 55555555 | 11 | 5555555589 | 1 |
30.06.2018 | 66666666 | 13 | 6666666645 | 1 |
30.06.2018 | 77777777 | 14 | 7777777742 | 1 |
Appreciate it!
Hello,
You can use Measures like:
TotalCount=COUNTROWS(Table)
CountsBefore15=COUNTROWS(FILTER(Table,[Hour]<15))
Ratio=DIVIDE([CountrsBefore15],[TotalCount])
This should work if you group by Date and ID.
Best regards.
Thank you for the reply. This will give me a percetage for sure, but I need this to check for each Id. I need to check for each Id on each date if there is an occurence before 15 and after 15, and then calculate that percetage for each date.
Hello,
so please add Date and Id to row content of your PivotTable.
I need to be able to aggregate this up to month, so I can't be using Date and Id in pivot (Diagram)
Can you provide expected result as table?
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
98 | |
96 | |
68 | |
67 |