cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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:

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 =

Return

_Weight
________________________________________________________________________

_WAVG =

//[Quantity] column is 1 or 0 in main fact table, where 1 is flag rows that should be included in calculation

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

Hi everyone,

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

``````_WAVG =

VAR _Single = MIN(JE_Q[Time_Spent])

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

VAR _zTable =
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!

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

Frequent Visitor

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!

Community Support

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.

Frequent Visitor

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

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

Frequent Visitor

Hi everyone,

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

``````_WAVG =

VAR _Single = MIN(JE_Q[Time_Spent])

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

VAR _zTable =
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!