Solved: Re: Average values then sum subtotal and grand tot...

Quinnie2017 · ‎06-28-2021

Hi Everyone - I've averaged a value but I want to sum the averages in my subtotals and totals.

Can you help?

mahoneypat · ‎06-28-2021

I would encourage you to add a separate Date table, instead of using a Date Hierarchy, but below is a measure expression that sums the averages.

Avg Impact =
SUMX (
SUMMARIZE ( MASTER, MASTER[Date].[Year], MASTER[Date].[Quarter] ),
CALCULATE ( AVERAGE ( MASTER[Impact%] ) )
)

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

mahoneypat · ‎06-28-2021

I would encourage you to add a separate Date table, instead of using a Date Hierarchy, but below is a measure expression that sums the averages.

Avg Impact =
SUMX (
SUMMARIZE ( MASTER, MASTER[Date].[Year], MASTER[Date].[Quarter] ),
CALCULATE ( AVERAGE ( MASTER[Impact%] ) )
)

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

Quinnie2017 · ‎06-29-2021

@mahoneypatholy cow did Patrick Mahomes just reply to my PowerBI question?!?!?!? LOL

Thank you sir, marking as solution.

Average values then sum subtotal and grand total