cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

## Show average of column values in grand total row

Hi,

I will try to explain my issue by taking an example. Please refer below screenshot. I need help to implement Yellow highlighted part.

There is a column called "Result" which is a measure by dividing Column A and Column B.

In the Grand total row of the matrix, I want to show "Result" value as an Average.

So from the above screenshot value, I want value as 3.5. But instead I am getting 4.4 because 550/125.

How to achieve 3.5 as a value in "Result" grand total.

1 ACCEPTED SOLUTION
Helper I

Hi,

I figured out the solution in case any one needed this. This may not be optimized solution but one can optimize as per the requirement.

So I have created four measures basically.

``````MeasureA =SUM('Sample'[ColumnA])

MeasureB = SUM('Sample'[ColumnB])

IntermediateMeasure = MeasureA/MeasureB

ResultMeasure =

VAR TotalResults = SUM('Sample'[ColumnA]) / SUM('Sample'[ColumnB])
VAR DistinctCategoryCount = DISTINCTCOUNT('Sample'[Category])
RETURN
IF(
ISFILTERED('Sample'[Category]),
DIVIDE(TotalResults, DistinctCategoryCount),
AVERAGEX(VALUES('Sample'[Category]), [IntermediateMeasure ])
)``````

Here is the screenshot of the result :

5 REPLIES 5
Helper I

Hi,

I figured out the solution in case any one needed this. This may not be optimized solution but one can optimize as per the requirement.

So I have created four measures basically.

``````MeasureA =SUM('Sample'[ColumnA])

MeasureB = SUM('Sample'[ColumnB])

IntermediateMeasure = MeasureA/MeasureB

ResultMeasure =

VAR TotalResults = SUM('Sample'[ColumnA]) / SUM('Sample'[ColumnB])
VAR DistinctCategoryCount = DISTINCTCOUNT('Sample'[Category])
RETURN
IF(
ISFILTERED('Sample'[Category]),
DIVIDE(TotalResults, DistinctCategoryCount),
AVERAGEX(VALUES('Sample'[Category]), [IntermediateMeasure ])
)``````

Here is the screenshot of the result :

Super User

Hello @Kandarp ,

Use AVERAGEX instead of AVERAGE directly to get the overall average work the same.

``Average = AVERAGEX('Table','Table'[A]/'Table'[B])``

Output looks like this:

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

Helper I

@Kishore_KVN Thanks this is perfect solution. But It will not work if we have multiple row with same category. In my case I have mutliple rows with same category name.

For example, if we have below data :

Then suggested solution will not work. Expected result is 4.695 but this formula shows 5.84. See below :

If you have any solution on this, please suggest. Thanks in advance.

Super User

Hello @Kandarp  in that case you have to add grouping for the category.

``Average = Calculate(AVERAGEX('Table',sum('Table'[A])/sum('Table'[B])),ALLEXCEPT(Table,'Table'[Category]))``

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

Helper I

@Kishore_KVN Unfortunately it is giving the same result :

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors