Average of hour based on selection in slicer

[NG1407]

I need to do below in power BI
1. There is a slicer with name hour which is a dropdown. So whatever number I select it should automatically calculate for selected Hour - 1, selected hour and selected hour +1 for ex. if I select 2 it should calculate average of Rate for 1,2 and 3

2. There is another slicer with type between and name Event Hour. So whatever number range is there in slicer it should subtract "Rate" from "calculated average" and then calculate average of thae difference .e.g below is the table. Let we select 2 from Hour slicer then it should calculate average of 1,2 and 3 i.e. 1234.12+3345.23+567.10 = 5146.45/3 =1715.4833
Then when we select Event Hour from another slicer it should subtract its Rate from 1715.4833. e.g. we select 1 and 2 from event hour slicer then 1715.4833-3345.23 = -1629.7467 and so on

Hour	Event Hour	Rate	differences
1	0	1234.12
2	1	3345.23	-1629.7467
3	2	2567.10	1148.3833
4	3	5436.56
5	4	7843.80

then we need to find average of differences calculated above. -1629.7467 + 1148.3833 /2

[v-nmadadi-msft]

Hi @NG1407 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks 

[v-nmadadi-msft]

Hi @NG1407 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-nmadadi-msft]

Hi @NG1407 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[BeaBF · Accepted solution]

@NG1407 Hi!

1. On column Hour as a dropdown, single-select.

2. Create a measure:

SelectedHourAverage =
VAR SelectedHour = SELECTEDVALUE(YourTable[Hour])
VAR AvgRate =
AVERAGEX(
FILTER(
YourTable,
YourTable[Hour] IN { SelectedHour - 1, SelectedHour, SelectedHour + 1 }
),
YourTable[Rate]
)
RETURN
AvgRate

3. On column Event Hour, as a Between slicer or multi-select.

4. Create a measure:

AverageDifference =
VAR SelectedAvgRate =
VAR SelectedHour = SELECTEDVALUE(YourTable[Hour])
RETURN
AVERAGEX(
FILTER(
ALL(YourTable),
YourTable[Hour] IN { SelectedHour - 1, SelectedHour, SelectedHour + 1 }
),
YourTable[Rate]
)
VAR DifferencesTable =
ADDCOLUMNS(
FILTER(
ALL(YourTable),
YourTable[Event Hour] IN VALUES(YourTable[Event Hour])
),
"Difference", YourTable[Rate] - SelectedAvgRate
)
VAR AverageDiff =
AVERAGEX(
DifferencesTable,
[Difference]
)
RETURN
AverageDiff

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!