Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear all,
Let's look at this sample data:
ID | DATE | TIME |
A | 2019-11-01 | 13:00 |
B | 2019-11-01 | 13:00 |
B | 2019-11-01 | 14:00 |
C | 2019-11-01 | 14:00 |
A | 2019-11-02 | 13:00 |
B | 2019-11-02 | 13:00 |
B | 2019-11-02 | 16:00 |
We can see that wee have this total number of occurrences per each hour per day:
2019-11-01:
13:00 - 2 occurrences
14:00 - 2 occurrences
2019-11-02:
13:00 - 2 occurrences
16:00 - 1 occurence
We do have a total number of 2 days, right?
I would like to have the sum of of occurences by hour per day, divided by the total number of days.
So that's the result that I would like to have:
13:00 - Average: 2 <--- (2+2)/2
14:00 - Average: 1 <--- 2/2
16:00 - Average: 0.5 <--- 1/2
Everything that I've tried I came up with the following result:
13:00 - Average: 2 <--- (2+2)/2
14:00 - Average: 2 <--- 2/1
16:00 - Average: 1 <--- 1/1
It happens because it divide the sum of occurences by the number of days that contains that hours, but I would like to, in fact, divide it by the total number of days that exists in the table.
Does someone have an idea how could I solve it?
It seems to me that I should first come up with a Measure that count the number of days but is independent of the time.
Note: The data is a bit more complicated, the time has hours, minutes, seconds and I have a Time Dimension Table to summarize it by complete hours.
Thanks in advance!
Solved! Go to Solution.
Hey guys, appreciate your help!
Unfortunately I couldn't get the desired result with your tips.
However, another person helped me and I could figure out how to solve it.
This is the solution:
formula1
Occurrences = COUNTA( DataTable[id] )
formula2
CountDays = CALCULATE ( DISTINCTCOUNT ( DataTable[date] ), ALLSELECTED ( 'DataTable' ) )
formula3
Avg_Occurrences = DIVIDE( [Occurences] , [CountDays] )
Now I can use the Avg_Occurrences measure in a matrix, using a Dimension Table column for the hours.
One more time, thank you for your help!
Hey guys, appreciate your help!
Unfortunately I couldn't get the desired result with your tips.
However, another person helped me and I could figure out how to solve it.
This is the solution:
formula1
Occurrences = COUNTA( DataTable[id] )
formula2
CountDays = CALCULATE ( DISTINCTCOUNT ( DataTable[date] ), ALLSELECTED ( 'DataTable' ) )
formula3
Avg_Occurrences = DIVIDE( [Occurences] , [CountDays] )
Now I can use the Avg_Occurrences measure in a matrix, using a Dimension Table column for the hours.
One more time, thank you for your help!
Hi @Anonymous ,
Glad to hear that. Please accept your reply above as a solution so that people who may have the same question can get the solution directly.
Best Regards,
Icey
Hi @Anonymous ,
You can create your measure like so:
Avg =
VAR CountOfTime =
CALCULATE (
COUNT ( 'Table'[ID] ),
ALLEXCEPT ( 'Table', 'Table'[DATE], 'Table'[TIME] )
)
VAR DistinctCountOfDate =
CALCULATE ( DISTINCTCOUNT ( 'Table'[DATE] ), ALL ( 'Table' ) )
RETURN
DIVIDE ( CountOfTime, DistinctCountOfDate )
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You need to count and Avg. Try this
formula 1
Count = SUM('Table'[Hours])
formula 2
Sum Amount =
AverageX(
VALUES(Table[Date]) // count till Day level
, [Count]
)
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Hey @amitchandak , thanks for replying.
Couldn't get my desired result with your tip.
Let me provide you a more realistic sample data.
DataTable:
ID;DATE;HOUR
715;2019-10-19;15:47:37
181;2019-10-19;15:56:11
349;2019-10-19;15:57:25
6ec;2019-10-19;15:58:16
57e;2019-10-19;16:02:35
860;2019-10-19;16:03:42
7a5;2019-10-19;16:03:52
978;2019-10-19;16:05:19
da0;2019-10-20;11:00:45
c2d;2019-10-20;23:04:53
355;2019-10-20;23:04:53
4f5;2019-10-20;23:05:10
396;2019-10-21;14:42:24
5f7;2019-10-21;14:43:37
93a;2019-10-21;14:55:36
a44;2019-10-21;14:59:21
264;2019-10-21;15:05:20
f48;2019-10-21;15:07:01
And a summarized Dimension Table with the values present in DataTable:
DimHourTable:
COMPLETEHOUR;HOUR24
15:47:37;15
15:56:11;15
15:57:25;15
15:58:16;15
16:02:35;16
16:03:42;16
16:03:52;16
16:05:19;16
11:00:45;11
23:04:53;23
23:04:53;23
23:05:10;23
14:42:24;14
14:43:37;14
14:55:36;14
14:59:21;14
15:05:20;15
15:07:01;15
Note: Relationship with Both Directions filter between DataTable[HOUR] and DimHourTable[COMPLETEHOUR]
I'm now doing this:
formula1
Occurrences = COUNTA( DataTable[id] )
formula2
CountDays = DISTINCTCOUNT( DataTable[date] )
formula3
Avg_Occurrences = DIVIDE( [Occurences] , [CountDays] )
Then I'm putting in a matrix the following
Rows: DimHourTable[HOUR24]
Values: Avg_Occurrences
With that Sample Data, this is the average I'm getting.
11 -> 1
14 -> 4
15 -> 3
16 -> 4
23 -> 3
But, in reality, I would like to have this:
11 -> 0.33
14 -> 1.33
15 -> 2
16 -> 1.33
23 -> 1
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |