Average of data for selected value from 2 different slicer

[NG1407]

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

[v-bmanikante]

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

[v-bmanikante]

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

[v-bmanikante]

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
 

[v-bmanikante · Accepted solution]

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]),

    0

)

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