Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Community!
I need help with develop calculation.
I have data which shows item usage (with datetime).
Mockup:
item_id | calculation result | use | datetime |
1 | 10 | 1 | 2019-11-16 12:00:00 |
1 | 12 | 1 | 2019-11-16 12:15:00 |
1 | 6 | 1 | 2019-11-16 13:50:00 |
2 | 10 | 1 | 2019-11-16 08:00:00 |
Calculation is simple avg.
I also have dim table, which help me define HourOfDay based on datetime column.
I wish to create new calculation, which is per item, but only for peak hours. Peak hour is hour which biggest item usage.
For item_id=1 peak hour = 12, for item_id = 2 -> 08
I manage to define output table which looks like this:
item_id | sum_usage | HourOfDay |
1 | 2 | 12 |
1 | 1 | 13 |
2 | 1 | 8 |
I have final table which looks like this:
item_id | calculation | calculation last 20 days | calculation last 20 days peak hours |
1 | 9 | 9 | should be 11 |
2 | 10 | 10 | should be 10 |
current calculation for last 20 days:
calculation last 20 days =
CALCULATE([calculation];all(Table[datetime]);Table[datetime]>=today()-20)
Should I create some kind of virtual aggregated table to get max peak hour and use it in calculation as new variable?
Regards!
Solved! Go to Solution.
Hi @Anonymous ,
If I don't misunderstand, you can try this:
Create measures:
Measure =
VAR CurrentValue = [sum_usage]
VAR MaxValue =
CALCULATE (
MAXX ( 'Table', [sum_usage] ),
ALLEXCEPT ( 'Table', 'Table'[item_id] )
)
RETURN
IF ( CurrentValue = MaxValue, 1 )
calculation last 20 days peak hours =
CALCULATE (
DIVIDE (
CALCULATE (
SUM ( 'Table'[calculation result] ),
FILTER ( 'Table', [Measure] <> BLANK () )
),
CALCULATE ( [sum_usage], FILTER ( 'Table', [Measure] <> BLANK () ) )
),
FILTER ( 'Table', 'Table'[datetime] >= TODAY () - 20 )
)
PBIX file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
If I don't misunderstand, you can try this:
Create measures:
Measure =
VAR CurrentValue = [sum_usage]
VAR MaxValue =
CALCULATE (
MAXX ( 'Table', [sum_usage] ),
ALLEXCEPT ( 'Table', 'Table'[item_id] )
)
RETURN
IF ( CurrentValue = MaxValue, 1 )
calculation last 20 days peak hours =
CALCULATE (
DIVIDE (
CALCULATE (
SUM ( 'Table'[calculation result] ),
FILTER ( 'Table', [Measure] <> BLANK () )
),
CALCULATE ( [sum_usage], FILTER ( 'Table', [Measure] <> BLANK () ) )
),
FILTER ( 'Table', 'Table'[datetime] >= TODAY () - 20 )
)
PBIX file attached.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
97 | |
88 | |
76 | |
70 | |
63 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |