Forum Discussion

sliceNdiceUup's avatar
sliceNdiceUup
Frequent Visitor
5 years ago
Solved

Dynamic Average calculation based on slicer selection (representing time horizon)

They say a picture says more than 1000 words. Below is what I am hoping to achieve.

 

Have both Sales and a Constant line representing average in a chart but depending on slicers such as region or selection of Financial Years I would like the average to be calculated and shown as a horizontal line for the period. 

Is there any way to achieve this in powerpivot/bi? 

 

My challenge is if I tell it to ignore the row filters representing years then the years modified by slicers will also be ignored so it's a catch of 22..  šŸ˜ž 

Copy of AvguSales:= CALCULATE(AVERAGE('AllActSales_table2'[SALES]),ALLEXCEPT(AllActSales_table,AllActSales_table[Period]))

 

Any ideas would be greatly appreciated.

Need Help
  • sliceNdiceUup's avatar
    sliceNdiceUup
    5 years ago

    Fowmy 

    Ok here is the solution / workaround that does the job. If there is any better way please let me know:

    I created an extra disconnected Calendar table and created slicers for both the disconnected calendar and the connected calendar.

    With VBA (busted, yes I work in Excel, my organisation is not ready for PowerBi as of yet) so that only 1 slicer is used I am able to have one slicer control the other, this way the two calendars can remain disconnected.

    My 2 Measures are:

    • LotSale:=CALCULATE(SUM(AllActSales_table[SALES (LOTS)]),

    FILTER(AllActSales_table,

    AllActSales_table[Period] >= MIN(discCalendar[Date]) &&

    AllActSales_table[Period] <= MAX(discCalendar[Date])))

    • AvgYield:=CALCULATE(DIVIDE( [LotSale], COUNTROWS(VALUES('discCalendar'[Year]))),

    ALLEXCEPT('Calendar',Calendar[Date]))

     

    Now depending how the user ajusts the slicers the average (constant horizontal line) for the selected years (and region) will recalibrate:

     

     

     

     

     

3 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    sliceNdiceUup 

    This measure should work for you, please try:

    Avg Sales = 

CALCULATE(
    AVERAGE('AllActSales_table2'[SALES]),
    ALLSELECTED(AllActSales_table[Period])
)
    • sliceNdiceUup's avatar
      sliceNdiceUup
      Frequent Visitor
      5 years ago

      @Fomy

      Thanks mate, appreciate that.

      Unfortunately with that formula you allow the row filters representing years within the pivot table to work. Please see image below with your formula and my "desired result". I would like to ignore row filters representing years (on the pivot table) but actually don't ignore it when calculating the average over the selected years via the slicers. I know it's a bit of contradicting requirement which is why I am not sure how to workaround it.

       

       

      • sliceNdiceUup's avatar
        sliceNdiceUup
        Frequent Visitor
        5 years ago

        Fowmy 

        Ok here is the solution / workaround that does the job. If there is any better way please let me know:

        I created an extra disconnected Calendar table and created slicers for both the disconnected calendar and the connected calendar.

        With VBA (busted, yes I work in Excel, my organisation is not ready for PowerBi as of yet) so that only 1 slicer is used I am able to have one slicer control the other, this way the two calendars can remain disconnected.

        My 2 Measures are:

        • LotSale:=CALCULATE(SUM(AllActSales_table[SALES (LOTS)]),

        FILTER(AllActSales_table,

        AllActSales_table[Period] >= MIN(discCalendar[Date]) &&

        AllActSales_table[Period] <= MAX(discCalendar[Date])))

        • AvgYield:=CALCULATE(DIVIDE( [LotSale], COUNTROWS(VALUES('discCalendar'[Year]))),

        ALLEXCEPT('Calendar',Calendar[Date]))

         

        Now depending how the user ajusts the slicers the average (constant horizontal line) for the selected years (and region) will recalibrate: