cancel
Showing results for
Did you mean:
Helper I

## Need help to calculate simple average as grand total

Hi,

I need help on calculating simple average as grand total. Like example below, I want grand total to be (17%+50%+67%+30%) divided by 4 projects = 41%. I do not want (\$7 divided by \$21 = 33%). How do I put this as a measure?

P/S 'Return (%)' = 'Revenue (\$)' divided by 'Expense (\$)'

1 ACCEPTED SOLUTION
Anonymous
Not applicable

``````Simple Average =
VAR _ratio = DIVIDE(SUM(data1[Revenue]),SUM(data1[Expenses]))
VAR _table = SUMMARIZE(data1,data1[Department Description],data1[Project],"total",DIVIDE(SUM(data1[Revenue]),SUM(data1[Expenses])))
RETURN IF(HASONEVALUE(data1[Project]),_ratio,AVERAGEX(_table,[total]))``````
8 REPLIES 8
Community Champion

didnt you try to set Return Filed aggrageation as Average? will it be ok?

do not hesitate to give a kudo to useful posts and mark solutions as solution

do not hesitate to give a kudo to useful posts and mark solutions as solution
Helper I

Hi az38,

As Return% in itself is a measure, there is no such option to "Average" it when I right click.

Grace

Anonymous
Not applicable

``Measure = AVERAGEX('Table',DIVIDE('Table'[Revenue],'Table'[Expense],0))``
Helper I

Hello @Anonymous ,

I tried using your formula but it returns the below. I would like Simple Average % to appear as 7.355% [(7.71% +7.00%)/2]. Any idea?

Anonymous
Not applicable

@gracechong Please refer the attached image I'm getting the correct average. can you please explain problem statement in more details. may be you can share some sample data along with expected result.

Helper I

Hi @Anonymous ,

I have uploaded the pbix with the sample dataset for your reference below.

The part that I need to figure out is on the measure for Simple Average %. The simple average % at project levels are correctly displayed in the visual table (they should be the same as return % at project level). However, the grand total for simple average % is incorrect. I would like to show it as sum of simple average % at project level when filters are applied in visual table; divided by count of projects when filters are applied in visual table. Example would be like snapshot below.

Grace

Anonymous
Not applicable

``````Simple Average =
VAR _ratio = DIVIDE(SUM(data1[Revenue]),SUM(data1[Expenses]))
VAR _table = SUMMARIZE(data1,data1[Department Description],data1[Project],"total",DIVIDE(SUM(data1[Revenue]),SUM(data1[Expenses])))
RETURN IF(HASONEVALUE(data1[Project]),_ratio,AVERAGEX(_table,[total]))``````
Helper I

Thank you @Anonymous ! This works exactly like how I wanted it! Appreciate it!

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors