Forum Discussion
rURAq
2 years agoFrequent 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/ca...
- 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
2 years agoFrequent 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])
)
))
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!