DAX Dynamic Date Averages

0
votes

I am trying to create a single measure that will calculate a date average, depending on the drill down on a time barchart.

The bar chart has 5 time pieces in the hierarchy. Year - Quarter - Month - Week -Day

As I drill through the bar chart, I want a measure that will dynamically calculate the Yearly Avg - Quarterly Avg - Monthly Avg, Weekly Avg, Daily Avg.

For example, lets say the barchart is on the Year Level and displaying 4 bars representing 2016-2019. The vale of the measure would be COUNT(UnitID) / 4 because there are 4 bars currently displayed on the X axis.

Lets drill into 2018 to the month level. There are 12 bars representing Jan-Dec. The value of the measure would be COUNT(2018 UnitIDs) / 12 because there are 12 bars currently displayed on the X axis.

Lets drill into 2019 to the month level. There are only 10 bars representing Jan-Oct. The value of the measure would be COUNT(2019 UnitIDs) / 10 because there are only 10 bars currently displayed on the X axis.

Finally, lets not drill down, but just expand the hierarchy from year to month. We go from the yearly view showing 4 bars to the monthly view showing 12. But the Jan total is the sum of 2016Jan + 2017Jan + 2018Jan + 2019Jan. The measure needs to interpret this as COUNT(All UnitIDs) / 12 because there are 12 months currently displayed on the X Axis.

I'm basically trying to figure out how to create a measure that counts whatever number of values are on the X axis at any given time.

Thank you all for your advice and feedback. I'm really looking forward to testing your responses and seeing if they work!

1
powerbidax

1 Answers

0
votes

It sounds like you are looking for a way to show the same value for each year, quarter, month, etc.

Figure1

I wonder how this would make sense as a visual, but if that is the case, your measure would be something like this.

Average Count UnitIDs in Drilldown Periods = 

-- At which level the report is drilled down?
VAR CalendarDrilldownLevel = 
IF(ISFILTERED('Calendar'[Day]), "Day",
    IF(ISFILTERED('Calendar'[Week]), "Week",
        IF(ISFILTERED('Calendar'[Month]), "Month",
            IF(ISFILTERED('Calendar'[Quarter]), "Quarter",
                IF(ISFILTERED('Calendar'[Year]), "Year", "None")))))

-- Total count of UnitIDs in the entire period
VAR TotalCount = CALCULATE([Count of Unit IDs], ALLSELECTED('Calendar'))

-- Number of years, quarters, months, etc. based on the drilldown level
VAR CountOfPeriods = CALCULATE(
    SWITCH(
        CalendarDrilldownLevel,
        "Year", DISTINCTCOUNT('Calendar'[Year]),
        "Quarter", COUNTROWS(
            DISTINCT(
                SELECTCOLUMNS(
                    'Calendar',
                    "Year", 'Calendar'[Year],
                    "Quarter", 'Calendar'[Quarter]
                )
            )
        ),
        "Month", COUNTROWS(
            DISTINCT(
                SELECTCOLUMNS(
                    'Calendar',
                    "Year", 'Calendar'[Year],
                    "Quarter", 'Calendar'[Quarter],
                    "Month", 'Calendar'[Month]
                )
            )
        ),
        -- Similar lines follow for weeks, days, and in case of no drill down.
    ),
    ALLSELECTED('Calendar')
)

RETURN DIVIDE(TotalCount, CountOfPeriods)

Maybe you are trying this to show average line in the visual? In that case, you can just define a simple measure like COUNT('Your Table'[UnitID]) and add an average line in column chart visual setting. (You can find that in Analytics section)

Figure2