Add an average line to line and stacked column chart

[clubspec]

Hi Gurus,

I want to add an average Qty line like below picture on a "line and stacked column chart".  The Qty table is linked to a calendar table.  And there is a slicer for 'Calendar' [Year].  How can I write the dax for this average line even when I drill up or down to see average by year or by quarter.

Appreciate you help in advance, I can't get my head around.

Thanks,

clubspec

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please try using ISINSCOPE DAX function in the measure, something like below.

The visualization has drill down arrow and you can try to click and drill down to year-quarter or to year-month.

ISINSCOPE function (DAX) - DAX | Microsoft Learn

Average sales: = 
SWITCH (
    TRUE (),
    ISINSCOPE ( 'calendar'[Year-MOnth] ),
        AVERAGEX (
            ALL (
                'calendar'[Year-MOnth],
                'calendar'[Year-Month sort],
                'calendar'[Year-Quarter]
            ),
            [Sales total:]
        ),
    ISINSCOPE ( 'calendar'[Year-Quarter] ),
        AVERAGEX (
            ALL ( 'calendar'[Year-Quarter] ),
            [Sales total:]
        ),
    ISINSCOPE ( 'calendar'[Year] ),
        AVERAGEX (
            ALL ( 'calendar'[Year] ),
            [Sales total:]
        )
)

[clubspec]

Thank you Jihwan_Kim, your dax returns same value as the column value:

Not sure if it is because my Qty table has more rows that got same date and in your example file your sales table has unique date.
I thought it would be something like CALCULATE(SUM(qty),ALL(month)) / DISTINTCOUNT (month) that has data, in this case CALCULATE(SUM(qty),ALL(month)) / 10 months.
But your ISINSCOPE dax definetly will help solving the dynamic of drilling up or down the year, quarter, and month.

[Jihwan_Kim]

Hi,

Thank you for your message, and I think it is because your calendar dimension table has more columns than what I have in my sample.

I think ISINSCOPE is one of DAX functions that you can use in this type of questions.

Thanks.

[clubspec · Accepted solution]

I got it working today but had to twist a little bit from your DAX to suit my case.  I cannot use AVERAGEX because my data contains multiple rows in same date or same month so if I used AVERAGEX it would be out.  I had to use SUM instead and created extra calculated columns on the fact table for the year, quarter, and month when transaction happended.  But yes, the ISINSCOPE definitely the way to go.

Average sales: =
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Calendar'[Month] ),
    DIVIDE(CALCULATE(SUM(SALESTABLE[Qty (kg)]),ALLEXCEPT('Calendar','Calendar'[Year])),CALCULATE(DISTINCTCOUNTNOBLANK(SALESTABLE[TRANS_MONTH]),ALLEXCEPT('Calendar','Calendar'[Year]))),
    ISINSCOPE ( 'Calendar'[Quarter] ),
    DIVIDE(CALCULATE(SUM(SALESTABLE[Qty (kg)]),ALLEXCEPT('Calendar','Calendar'[Year])),CALCULATE(DISTINCTCOUNTNOBLANK(SALESTABLE[TRANS_QTR]),ALLEXCEPT('Calendar','Calendar'[Year]))),
    ISINSCOPE ('Calendar'[Year]),
    DIVIDE(CALCULATE(SUM(SALESTABLE[Qty (kg)]),ALLSELECTED('Calendar'[Year])),CALCULATE(DISTINCTCOUNTNOBLANK(SALESTABLE[TRANS_YEAR]),ALLSELECTED('Calendar'[Year]))))