Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.

Get started- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations (Read-only)
- Power Platform and Dynamics 365 Integrations (Read-only)
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

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

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: Weighted average shows incorrect sum

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Weighted average shows incorrect sum

02-16-2024
12:42 AM

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!

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-16-2024
01:31 AM

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!

5 REPLIES 5

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-18-2024
10:17 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-18-2024
11:48 PM

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-19-2024
12:15 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-19-2024
12:30 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-16-2024
01:31 AM

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!

Featured Topics

Top Solution Authors

User | Count |
---|---|

87 | |

72 | |

68 | |

63 | |

54 |

Top Kudoed Authors

User | Count |
---|---|

98 | |

89 | |

74 | |

67 | |

62 |