Average based on month selection.

[TheSAY]

Hello,

I am trying to create a report for our membership numbers and show the average based on the selected month, but it doesn't seem to be working properly.  Here's the report breakdown:

1. Date table used for the month slicer.
2. CSV sheet of all membership types, their branch, the total units and the total members based on a date.

The membership software we use wasn't introduced until November of 2012, so certain months won't show for 2012.  I'm not sure if that has anything to do with it or not.

I've taken two membership types as an example to show what's not working.  The "Adult 18+" membership type has always been available.  The "BH Staff Adult" membership type was created in 2019.

When selecting month 1 for January, using the DIVIDE function shows the correct expected number for "Adult 18+" but does not show the correct expected number for "BH Staff Adult", and using the AVERAGE function does not show the correct expected number for "Adult 18+" but shows the correct expected number for "BH Staff Adult".

Since "BH Staff Adult" technically exists in 2019 (0 until April), it seems as if the DIVIDE function is picking up 2019 as well.  Everything from month 2 for February and on, seems to work fine, except for the AVERAGE function still not showing the "Adult 18+" correctly.

All I'm trying to do is figure out a way to show all the units, per year, and get the average unit number which should be the total amount of units divided by the number of years.  It should be coming out to something along the lines of:

Mem Type	2013	2014	2015	2016	2017	2018	2019	2020	Avg Units
Adult 18+	10	10	10	10	10	10	10	10	10 ( 80 / 8 )
BH Staff A							8	8	2 ( 16 / 8 )
Total	10	10	10	10	10	10	18	18	12 ( 96 / 8 )

You can download my files at: https://www.dropbox.com/s/28amoi1hp1ioej9/Updated%20Membership%20Information.zip?dl=0

Any help would be appreciated.

[v-gizhi-msft]

Hi,

Please try to change your original DIVIDED measure to this:

Divided Units = CALCULATE(DIVIDE([Units],[YearCount]),ALLSELECTED(Members2[Month]))

The result shows:(In this case the denominator is 8 for these two types as you expect in your screenshot)

 

Hope this helps.

Best Regards,

Giotto Zhi

[TheSAY]

Hi @v-gizhi-msft ,

I think I may have explained it a little wrong, especially for the second number.

The "BH Staff Adult" should be coming out to 34, as it's only 1 year with data, not all 8.  In the table example, it should have been 8 ( 16 / 2 ), not 2.

So, it should be calculating the average based on the SUM of all the years and DIVIDED by the amount of actual years.  For instance, if another membership type only had data for 2018, 2019 and 2020, it would be that amount divided by 3.

[v-gizhi-msft · Accepted solution]

Hi,

After a lot test and research, i have two ways to solve your issue:

One:

Create a check column in table 'Member2':

Check = IF(Members2[Current Units]=BLANK(),1,0)

Then create a measure:

Measure = 
VAR a =
    FILTER (
        SUMMARIZE (
            Members2,
            Members2[Year],
            Members2[Membership Type],
            "Check", SUM ( Members2[Check] ),
            "Units", CALCULATE (
                SUM ( Members2[Current Units] ),
                FILTER ( Members2, VALUE ( Members2[Month] ) = SELECTEDVALUE ( Dates[Month] ) )
            )
        ),
        Members2[Membership Type] = "Adult 18+"
            || Members2[Membership Type] = "BH Staff Adult"
    )
RETURN
    SUMX ( a, [Units] )
        / COUNTROWS ( FILTER ( a, [Check] = 0 && [Units] <> 0 ) )

The result shows:

Two:

Change your original YearCount measure to this:

YearCount = CALCULATE(DISTINCTCOUNT('Members2'[Year]),NOT(ISBLANK('Members2'[Current Units])))

Create a measure:

Measure 2 = IF(ISINSCOPE(Members2[Year]),[Units],[Divided Units])

Add this measure to the matrix visual, the result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[TheSAY]

Thank you!  That works perfectly!  Option two seems to be easiest, but I can use option one for other reports that I plan on creating for more of a comparison check.

I've gone ahead and accepted this as the answer!