Weighted average shows incorrect sum

[rURAq]

Hi everyone,

I'm trying to to calculate weighted average for our analysis around how much time we spend on certain process and my total is not returning correct value. Calculation for each row/category is correct, but total number is wrong:

correct value is ~32.71, not 25.00.

Time_Spent	#Comments	%	_Tot_Comments	_WAVG_Weight	_WAVG
1	1	0.02%	4268	0.02%	0.00
2	29	0.68%	4268	0.68%	0.01
5	404	9.47%	4268	9.47%	0.47
7	2	0.05%	4268	0.05%	0.00
8	131	3.07%	4268	3.07%	0.25
10	1,202	28.16%	4268	28.16%	2.82
15	410	9.61%	4268	9.61%	1.44
20	235	5.51%	4268	5.51%	1.10
25	39	0.91%	4268	0.91%	0.23
30	1,032	24.18%	4268	24.18%	7.25
40	49	1.15%	4268	1.15%	0.46
45	107	2.51%	4268	2.51%	1.13
50	5	0.12%	4268	0.12%	0.06
55	4	0.09%	4268	0.09%	0.05
60	274	6.42%	4268	6.42%	3.85
80	3	0.07%	4268	0.07%	0.06
90	19	0.45%	4268	0.45%	0.40
100	4	0.09%	4268	0.09%	0.09
120	151	3.54%	4268	3.54%	4.25
150	11	0.26%	4268	0.26%	0.39
180	54	1.27%	4268	1.27%	2.28
240	96	2.25%	4268	2.25%	5.40
300	1	0.02%	4268	0.02%	0.07
400	1	0.02%	4268	0.02%	0.09
600	4	0.09%	4268	0.09%	0.56
	4,268	100%		100%	32.71

There are 4,268 comments (replies from our questionnaires), which I use as weight. Then apply this weight on each individual [time_spent] minutes which works fine, but then sum up for total is the last piece I cannot crack.

I tried numerous different calculations bellow, but they always return either with 1 or 25.00

_WAVG_Weight = 

            VAR _Tot_Comments = CALCULATE(sum(JE_Q[Quantity]),ALLEXCEPT(JE_Q,JE_Q[Quantity]),JE_Q[Time_Spent]<>BLANK())

            VAR _Comments = SUM(JE_Q[Q_Quantity])

            VAR _Weight = DIVIDE(_Comments,_Tot_Comments)

    Return

    _Weight
________________________________________________________________________

_WAVG = 
    
    VAR _Tot_Comments = CALCULATE(sum(JE_Q[Quantity]),ALLEXCEPT(JE_Q,JE_Q[Quantity]))
    //[Quantity] column is 1 or 0 in main fact table, where 1 is flag rows that should be included in calculation

    VAR _Comments = SUM(JE_Q[Q_Quantity])

    VAR _Weight_Per = DIVIDE(_Comments,_Tot_Comments)

    VAR _Single = MIN(JE_Q[Time_Spent])
    //[Time_Spent] are only numerical values from 1 to 600 also with blanks, which are excluded by [Quantity] flag. blanks are flagged 0.

    VAR _WA = SUMX(JE_Q,_Single*[_WAVG_Weight])

    VAR _Filtered_Table = FILTER(JE_Q,JE_Q[Quantity]=1)

    VAR _zTable =    
        ADDCOLUMNS(
        SUMMARIZE(
            JE_Q,
            JE_Q[Time_Spent]),
        "x",_WA
        )

    RETURN

    //SUMX(VALUES(JE_Q[Time_Spent]), _Single*[_WAVG_Weight])
    
IF(HASONEVALUE(JE_Q[Time_Spent]),SUMX(VALUES(JE_Q[Time_Spent]),CALCULATE(SUMX(_zTable,_Weight_Per * _Single))),CALCULATE(SUMX(_zTable,_Weight_Per * _Single)))
    
    //SUMX(_zTable,_Weight_Per * _Single)

    //AVERAGEX(SUMMARIZE(_Filtered_Table,JE_Q[Time_Spent]),_WA)

    //IF(HASONEVALUE(JE_Q[Time_Spent]),_WA,SUMX(VALUES(JE_Q[Time_Spent]),_WA))

    //SUMX(JE_Q,_Single*[_WAVG_Weight])
    
    //CALCULATE(_Single*_Weight_Per))))))

Thanks for any hints!

[v-tianyich-msft]

Hi @rURAq ,

The overall look is great! But for calculating the occupancy ratio, you can try using the following expression:

%% = DIVIDE(MAX('Table'[#Comments]),MAX('Table'[_Tot_Comments]))

It also seems to be able to calculate the appropriate share of the situation for you.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[rURAq]

Hi @v-tianyich-msft,

thanks for your advice, much appreciated. 

isn't it the same calculation that I use as variable for weight percentage?

 VAR _Weight_Per = DIVIDE(_Comments,_Tot_Comments)

this is the part of code I'm trying to improve, especially the final part that returns total weighted average time spent:

    IF(HASONEVALUE(JE_Q[Time_Spent]),SUMX(VALUES(JE_Q[Time_Spent]),SUMX(_zTable,_Weight_Per * _Single)),    SUMX (
    VALUES ( JE_Q[Time_Spent]),
    CALCULATE (
        DIVIDE (
            SUM ( JE_Q[Quantity] ),
            CALCULATE (
                SUM ( JE_Q[Quantity] ),
               ALLSELECTED (JE_Q[Time_Spent])
            )
        )
            * AVERAGE ( JE_Q[Time_Spent])
    )
))

Thanks!

[v-tianyich-msft]

Hi @rURAq ,

Maybe I didn't quite understand what you meant, please check the attachment.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[rURAq]

Thanks @v-tianyich-msft,

I forgot to mention that table I shared is already a matrix out of ~5K rows in "JE_Q" table. And so those count of comments and sum of comments are not columns, so I can't use MIN/MAX.

that's why I use these 2 variables to get those numbers, but seems like it's not the suited well for all parts of my final expression.

    VAR _Tot_Comments = CALCULATE(sum(JE_Q[Quantity]),ALLEXCEPT(JE_Q,JE_Q[Quantity]))
    //[Quantity] column is 1 or 0 in main fact table, where 1 is flag rows that should be included in calculation

    VAR _Comments = SUM(JE_Q[Q_Quantity])

 **_Comments = #Comments

Sorry for not being clear enough from get-go...

[rURAq · Accepted solution]

Hi everyone,

so I managed to get total working now after finding this piece and added into my measure:

_WAVG = 
    
    VAR _Tot_Comments = CALCULATE(sum(JE_Q[Quantity]),ALLEXCEPT(JE_Q,JE_Q[Quantity]))

    VAR _Comments = SUM(JE_Q[Q_Quantity])

    VAR _Weight_Per = DIVIDE(_Comments,_Tot_Comments)

    VAR _Single = MIN(JE_Q[Time_Spent])

    VAR _WA = SUMX(JE_Q,_Single*[_WAVG_Weight])

    VAR _zTable =    
        ADDCOLUMNS(
        SUMMARIZE(
            JE_Q,
            JE_Q[Time_Spent]),
        "x",_WA
        )

    RETURN

    IF(HASONEVALUE(JE_Q[Time_Spent]),SUMX(VALUES(JE_Q[Time_Spent]),SUMX(_zTable,_Weight_Per * _Single)),    SUMX (
    VALUES ( JE_Q[Time_Spent]),
    CALCULATE (
        DIVIDE (
            SUM ( JE_Q[Quantity] ),
            CALCULATE (
                SUM ( JE_Q[Quantity] ),
               ALLSELECTED (JE_Q[Time_Spent])
            )
        )
            * AVERAGE ( JE_Q[Time_Spent])
    )
))
    

TBH, I do not get it why it is working, I just realized that in my previous formulas I used MIN() in calculation for total, which was wrong and returned 25, what was just count of all categories, but is there a better/cleaner way how to write this?

Thanks!