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 ...
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:

     

     

     

     

     

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: