Average calculation with several conditions

[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

[NG1407]

Its not giving correct result. Its giving same value for all customers.

[v-aatheeque]

Hi @NG1407 ,

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Please show the expected outcome based on the sample data you provided.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

[maruthisp · Accepted solution]

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 

[123abc]

👍

[NG1407]

It is not working for my scenario

[Hakuna_matata]

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.

[v-aatheeque]

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!

[v-aatheeque]

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!

[v-aatheeque]

Hi @NG1407 ,

If our response addressed by the community member for  your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.

Thank you for being a part of the Microsoft Fabric Community Forum!