Forum Discussion

NG1407's avatar
NG1407
Icon for Advocate I rankAdvocate I
1 year ago
Solved

Average calculation with several conditions

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 selecte...
Hakuna_matata's avatar
Hakuna_matata
Icon for Resolver I rankResolver I
1 year ago

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's avatar
    v-aatheeque
    Icon for Community Support rankCommunity Support
    1 year ago

    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's avatar
      v-aatheeque
      Icon for Community Support rankCommunity Support
      1 year ago

      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's avatar
        v-aatheeque
        Icon for Community Support rankCommunity Support
        1 year ago

        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!