cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Afkkek
Frequent Visitor

Calculating an Average of previous rows in the Total

Hello, was having trouble with a calculation so thought I'd drop by here and see if anyone had any ideas to solve the issue.

 

In the below screenshot of an excel mockup, I have Column A which is a column from one table, and Column B which is a column from a different table, and then A/C which is just a measure which divides Column A by Column B.  This is repeated for each year.

 

Afkkek_0-1618862623154.png

 

The Grand Total would end up summing all of Column A and Column B, then repeating the measure's instructions of dividing A/C which I understand is what it's supposed to be doing, but any suggestions on how I would I go about it if I wanted to take a simple average of all the results of Column A/C?  The result of dividing the totals would be 0.05 in this case, but the average of (0.1, 0.025, 0.0833 and -0.05) would be 0.039583 in this example.

 

Thanks in advance!

1 ACCEPTED SOLUTION
FrankAT
Super User II
Super User II

Hi @Afkkek ,

here is my suggestion:

Sum of A = SUM('Table'[A])

Sum of B = SUM('Table'[B])

A/B = 
IF (
    HASONEVALUE ( 'Table'[Year] ),
    DIVIDE ( [Sum of A], [Sum of B] ),
    AVERAGEX ( 'Table', DIVIDE ( [Sum of A], [Sum of B] ) )
)

 

19-04-_2021_23-04-43.png

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

View solution in original post

2 REPLIES 2
FrankAT
Super User II
Super User II

Hi @Afkkek ,

here is my suggestion:

Sum of A = SUM('Table'[A])

Sum of B = SUM('Table'[B])

A/B = 
IF (
    HASONEVALUE ( 'Table'[Year] ),
    DIVIDE ( [Sum of A], [Sum of B] ),
    AVERAGEX ( 'Table', DIVIDE ( [Sum of A], [Sum of B] ) )
)

 

19-04-_2021_23-04-43.png

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

View solution in original post

Afkkek
Frequent Visitor

Thanks FrankAT! I ended up making it work on the side with a needlessly complicated approach, but your solution is much more intuitive.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors