Forum Discussion

rURAq's avatar
rURAq
Frequent Visitor
2 years ago
Solved

Weighted average shows incorrect sum

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/ca...
Need Help
Tips and Tricks
  • rURAq's avatar
    rURAq
    2 years ago

    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!

rURAq's avatar
rURAq
Frequent Visitor
2 years ago

Hi Anonymous,

 

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!

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    rURAq
    Frequent Visitor
    2 years ago

    Thanks Anonymous,

     

    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...