Forum Discussion

NG1407's avatar
NG1407
Advocate 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 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

9 Replies

  • maruthisp's avatar
    maruthisp
    Super User
    1 year ago

    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 

  • Hakuna_matata's avatar
    Hakuna_matata
    Resolver 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
      Community 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
        Community 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!

  • NG1407's avatar
    NG1407
    Advocate I
    1 year ago

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