Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
rURAq
Frequent Visitor

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/category is correct, but total number is wrong:

rURAq_1-1708072696201.png

correct value is ~32.71, not 25.00.

 

Time_Spent#Comments%_Tot_Comments_WAVG_Weight_WAVG
110.02%42680.02%0.00
2290.68%42680.68%0.01
54049.47%42689.47%0.47
720.05%42680.05%0.00
81313.07%42683.07%0.25
101,20228.16%426828.16%2.82
154109.61%42689.61%1.44
202355.51%42685.51%1.10
25390.91%42680.91%0.23
301,03224.18%426824.18%7.25
40491.15%42681.15%0.46
451072.51%42682.51%1.13
5050.12%42680.12%0.06
5540.09%42680.09%0.05
602746.42%42686.42%3.85
8030.07%42680.07%0.06
90190.45%42680.45%0.40
10040.09%42680.09%0.09
1201513.54%42683.54%4.25
150110.26%42680.26%0.39
180541.27%42681.27%2.28
240962.25%42682.25%5.40
30010.02%42680.02%0.07
40010.02%42680.02%0.09
60040.09%42680.09%0.56
 4,268100% 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!

1 ACCEPTED SOLUTION
rURAq
Frequent Visitor

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])
    )
))
    

 

 

rURAq_0-1708075323179.png

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!

View solution in original post

5 REPLIES 5
v-tianyich-msft
Community Support
Community Support

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.

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!

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.

 

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.

 

rURAq_0-1708331127635.png

 

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
Frequent Visitor

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])
    )
))
    

 

 

rURAq_0-1708075323179.png

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!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.