Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to do a calculation in the total row

Hi 

Totally new to BI so everything is new to me. I have this report that I have created:

russelln1_1-1661254877595.png

 

The 4 boxes in the centre below the white box display the totals for each column, which the system generates automatically.
Is there a way to display an Average figure automatically instead of a sum? So it adds them all up and then divide by 33 (that's the number of lines I'm reporting on). I.e. the first box is displaying 2959.63%, divide it by 33 and this will give me 89.69% which is the figure that I require to be displayed.

As I say I am new and a bit green.

Appreciate your help.

 

1 ACCEPTED SOLUTION
ToddChitt
Super User
Super User

Simple answer: Change the Aggregation in the Visual Setting from Sum to Average.

BUT, If the number you are aggregating is already a percentage, then it is usually NOT a good idea to show the "Average of the Percentage." Suppose you sell 500,000 of widget A at $1 each, and that average is $1. but you also sold 1 of widget B at $10. Your data may indicate that you have TWO averages, one per Widget type: $1 and $10. The Average of those two averages is $5.50 ($11 divided by 2). But the TRUE average is 500,010 divided by 500,001. To calculate that, you need the SUM of the Sales and the COUNT of Sales, and create a MEASURE that does that math with a DIVIDE statement.

Si

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Thanks for the response - Yes I know the math it's just how I apply that in to BI. Which, if I'm reading you right, is by creating a measure. Cool, I appreciate the direction.

Yes, a simple MEASURE.

And by the way, that MEASURE calculation will still exist at every sub-slice of data in that table, so if you slice/filter it by Product, or Date, or Customer, the calculation will only act on the rows of the slice, right down to a single row. 

You actually have to work hard to make your measure calculations look at something other than the current slice/context.

 

Please mark replie as Solved if appropriate.

Thanks

ToddChitt
Super User
Super User

Simple answer: Change the Aggregation in the Visual Setting from Sum to Average.

BUT, If the number you are aggregating is already a percentage, then it is usually NOT a good idea to show the "Average of the Percentage." Suppose you sell 500,000 of widget A at $1 each, and that average is $1. but you also sold 1 of widget B at $10. Your data may indicate that you have TWO averages, one per Widget type: $1 and $10. The Average of those two averages is $5.50 ($11 divided by 2). But the TRUE average is 500,010 divided by 500,001. To calculate that, you need the SUM of the Sales and the COUNT of Sales, and create a MEASURE that does that math with a DIVIDE statement.

Si

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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