topic Re: Average calculation with several conditions in DAX Commands and Tips

Average calculation with several conditions

NG1407 — Tue, 22 Apr 2025 14:14:03 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 calculation with several conditions

Hakuna_matata — Sun, 27 Apr 2025 09:11:57 GMT

Hi NG1407 ,

Here are the steps that should help you with your task ^_^:

1. Create a Measure for Hours:

First, you'll need to create a measure for the average rate within a specific hour range. Here's the formula for that:

AvgRateSelectedHourRange =

VAR SelectedHour = SELECTEDVALUE('Table 1 (Sheet1)'[Hour])

VAR HourRange =

FILTER(

ALL('Table 1 (Sheet1)'),

'Table 1 (Sheet1)'[Hour] >= SelectedHour - 1 &&

'Table 1 (Sheet1)'[Hour] <= SelectedHour + 1

)

RETURN

AVERAGEX(HourRange, 'Table 1 (Sheet1)'[Rate])

2. Create a Disconnected Table for Event Hours:

You will need to create a disconnected table that represents your event hours. This will allow for better control over filtering without affecting other data in your model.

3.Create a Measure for Calculating Differences Using the Event Hours Table:

AvgEventHourDifference =

VAR AvgRate = [AvgRateSelectedHourRange]

VAR SelectedEventHourStart = MINX(ALLSELECTED('EventHourSlicer'[Event Hour]), 'EventHourSlicer'[Event Hour])

VAR SelectedEventHourEnd = MAXX(ALLSELECTED('EventHourSlicer'[Event Hour]), 'EventHourSlicer'[Event Hour])

VAR EventHourDifferences =

FILTER(

ALL('Table 1 (Sheet1)'),

'Table 1 (Sheet1)'[Event Hour] >= SelectedEventHourStart &&

'Table 1 (Sheet1)'[Event Hour] <= SelectedEventHourEnd

)

VAR AverageDifference =

AVERAGEX(

EventHourDifferences,

ABS(AvgRate - 'Table 1 (Sheet1)'[Rate])

)

RETURN

AverageDifference

4.Add Visuals for the Event Hour Slicer:

Finally, use the disconnected table to add visuals for the event hour slicer. This will allow you to filter the data based on your selected event hours and display the corresponding results.

Hope it helps!

If this solution works for you, please mark it as a solution.

Re: Average calculation with several conditions

v-aatheeque — Sat, 03 May 2025 17:18:37 GMT

Hi NG1407 ,

We haven’t heard back from you regarding our previous response and wanted to check if your issue has been resolved.

If it has, please consider clicking “Accept Answer” and “Yes” if you found the response helpful.
If you still have any questions or need further assistance, feel free to let us know — we're happy to help!

Thank you!

Re: Average calculation with several conditions

v-aatheeque — Wed, 07 May 2025 17:37:50 GMT

Hi NG1407 ,

We haven’t heard back from you regarding our previous response and wanted to check if your issue has been resolved.

Thank you!

Re: Average calculation with several conditions

maruthisp — Thu, 08 May 2025 05:15:40 GMT

Hi NG1407,

I try to develop based on your requirements and please find the pbix file for you reference.

Average calculation with several conditions.pbix

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated — thanks!

Best Regards,

Maruthi

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/

X - Maruthi Siva Prasad - (@MaruthiSP) / X