Forum Discussion

TheSAY's avatar
TheSAY
Frequent Visitor
6 years ago
Solved

Average based on month selection.

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:   D...
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    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

v-gizhi-msft's avatar
v-gizhi-msft
Community Support
6 years ago

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's avatar
TheSAY
Frequent Visitor
6 years ago

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's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    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's avatar
      TheSAY
      Frequent Visitor
      6 years ago

      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!