The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.

**Save €200 with code MSCUST on top of early bird pricing!**

- 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
- 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
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

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

Showing results for

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- 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!

Announcements

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

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

Featured Topics

Top Solution Authors

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

110 | |

82 | |

63 | |

54 | |

52 |

Top Kudoed Authors

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

127 | |

118 | |

81 | |

65 | |

64 |