Finding Average Number of Months based on Quantity per Month

[river-phillips]

Hi all,

I'm attempting to display the average MonthsInSystem for each age group based on the quantity per MonthsInSystem. For example, take the following table:

AgeActual	MonthsInSystem	Value
12	0	29
12	1	114
12	2	78
12	3	24
12	4	2
12	5	34
12	6	57
12	7	65
12	8	52
12	9	49
12	10	42
12	11	70
12	12	80
12	13	35
12	14	36
12	15	36
12	16	28
12	17	22
12	18	25
12	19	30
12	20	11
12	21	8
12	22	12
12	23	15
12	24	35
12	25	7
12	26	13
12	27	5
12	29	7
12	30	2
12	31	2
12	32	4
Average Months in System	?

I want to definitively say 'For Age 12, the average number of months in system is X' but my average is currently only accounting for the MonthsInSystem (0-32) without the quantity per MonthsInSystem.

Any help with this will be appreciated!

[amitchandak]

@river-phillips , Not very clear. see if this can help

divide(sum(Table[Value]),sum(Table[MonthsInSystem]))

[river-phillips]

Hi @amitchandak thanks for the quick reply but that didn't work unfortunately.

Let me clarify a couple more things:

The "Values" column is actually a distinct count of users that fall within the AgeActual and MonthsInSystem category.

If I create a bar chart with the distinct students on the y axis and their total months in system on the x axis, the automatic 'average' line shows the correct value (see below). I'd like to create a measure/calculated column that displays the same value as shown in the bar chart.

Thank you

[v-yingjl]

Hi @river-phillips ,

To calculate the average of monthlnsystem for each ageactual, you can create this measure:

Average = 
VAR _monthlnsystem =
    CALCULATE (
        SUM ( 'Table'[MonthlnSystem] ),
        ALLEXCEPT ( 'Table', 'Table'[AgeActual] )
    )
VAR _countuser =
    CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[AgeActual] ) )
RETURN
    DIVIDE ( _monthlnsystem, _countuser, BLANK () )

Attached the sample file that hopes to help you: Finding Average Number of Months based on Quantity per Month.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[river-phillips]

Hi @v-yingjl ,

Thank you for the reply. It definitely put me in the right direction, but it's still pulling the incorrect average Your provided measure is the last column, "Average". I was able to create a measure that calculated total number of months for an age group (TotalStudentMonths), but I'm unable to divide the measure's sum by the total DistinctCount of UserID for that age group (see below)

I have the right numbers: 737 for DistinctCount of UserID and 3936 for the total student months. These would give the correct average (3916 / 737 = 5.313) but I cannot figure out how to divide the Sum of TotalStudentMonths measure by the Sum of all distinct UserIDs for the age group. Any additional help will be appreciated!

[v-yingjl · Accepted solution]

Hi @river-phillips ,

Since you have a [TotalStudentMonth] measure, you can try this measure:

Average = 
VAR _sum =
    CALCULATE (
        [TotalStdentMonths],
        ALLEXCEPT ( 'Table', 'Table'[AgeActual], 'Table'[UserId] )
    )
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[UserId] ),
        ALLEXCEPT ( 'Table', 'Table'[AgeActual] )
    )
RETURN
    DIVIDE ( _sum, _count, BLANK () )

Please refer this sample file: average.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.