topic Re: Average of data for selected value from 2 different slicer in DAX Commands and Tips

Average of data for selected value from 2 different slicer

NG1407 — Tue, 22 Apr 2025 13:58:43 GMT

I need solution for below scenario:

I have several slicer in my report. Date, Hour, Event hour, Customer etc.

Now when I select Hour from slicer then it should calculate average of rate for selected hour -1, selected hour, and selected hour +1 e.g. we select 3 from Hour slicer then it should calculate average of rate for hour 2 ,3 and 4

Then in Event hour slicer (its a between slicer) when we select the range say 2 to 3 then it should subtract Rate of Event hour 2 and event hour 3 from average of rate (calculated above)

and lastly we need to calculated average of these difference. Below is table

Hour	Event Hour	Rate
2	1	7654
3	2	3456
4	3	2313
5	4	4532
6	5	2659

Average for Hour 2,3 and 4= 7654+3456+2313/3 = 4474.3333
then we select 2 to 3 from Event Hour and subtract Rate from Average like 4474.3333-3456 = 1018.3333 and 4474.3333-2313= 2161.3333

then calculate average of these difference. 1018.3333+2161.3333/2

Re: Average of data for selected value from 2 different slicer

Anonymous — Mon, 28 Apr 2025 07:15:30 GMT

Hi NG1407 ,

Thank you for reaching out to Microsoft Fabric Community forum.

Apologies for the delay in getting back to you

Please try the below DAX queries.

avg rate =

VAR SelectedHour = SELECTEDVALUE('RatesTable'[Hour])

VAR HoursToConsider =

{SelectedHour - 1, SelectedHour, SelectedHour + 1}

RETURN

AVERAGEX(

FILTER(

ALL('RatesTable'),

'RatesTable'[Hour] IN HoursToConsider

'RatesTable'[Rate]

)

for slicer event hour:

Final Average Difference =

VAR SelectedHour = SELECTEDVALUE('RatesTable'[Hour])

VAR AvgRate =

AVERAGEX(

FILTER('RatesTable', 'RatesTable'[Hour] IN {SelectedHour - 1, SelectedHour, SelectedHour + 1}),

'RatesTable'[Rate]

)

VAR EventHourMin = MIN('RatesTable'[Event Hour])

VAR EventHourMax = MAX('RatesTable'[Event Hour])

VAR EventHoursSelected =

FILTER(

'RatesTable',

'RatesTable'[Event Hour] >= EventHourMin

&& 'RatesTable'[Event Hour] <= EventHourMax

)

VAR DifferenceTable =

ADDCOLUMNS(

EventHoursSelected,

"Difference", ABS(AvgRate - 'RatesTable'[Rate])

)

RETURN

IF(

COUNTROWS(DifferenceTable) > 0,

AVERAGEX(DifferenceTable, [Difference]),

)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy

Re: Average of data for selected value from 2 different slicer

Anonymous — Thu, 01 May 2025 07:36:38 GMT

Hi NG1407 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

B Manikanteswara Reddy

Re: Average of data for selected value from 2 different slicer

Anonymous — Mon, 05 May 2025 09:13:49 GMT

Hi NG1407 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

B Manikanteswara Reddy

Re: Average of data for selected value from 2 different slicer

Anonymous — Thu, 08 May 2025 11:33:55 GMT

Hi NG1407 ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

B Manikanteswara Reddy