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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Fred_T
Regular Visitor

Would like a sum total for an average column

Hi all,

 

I have a "stupid" problem : I have to sum an average cost column.

 

Example :

I have actually 

Fred_T_0-1635454426415.png 

All values are averages but, for my need, the total row is wrong. I don't want 2 for 2020 and 3 for 2021 but 6 for 2020 and 9 for 2021.

That means :

Fred_T_1-1635454540396.png

 

It will be validated too without rows headers.

Fred_T_2-1635454691012.png

 

Thank you for your help

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

Iterate the average calculate at the desired granularity and add it all up.

 

 

SUMX (
    VALUES ( Table1[ColABC] ),
    CALCULATE ( AVERAGE ( Table1[Value] ) )
)

 

Or if you already have an averaging measure:

SUMX ( VALUES ( Table1[ColABC] ), [AvgMeasure] )

 

View solution in original post

4 REPLIES 4
AlexisOlson
Super User
Super User

Iterate the average calculate at the desired granularity and add it all up.

 

 

SUMX (
    VALUES ( Table1[ColABC] ),
    CALCULATE ( AVERAGE ( Table1[Value] ) )
)

 

Or if you already have an averaging measure:

SUMX ( VALUES ( Table1[ColABC] ), [AvgMeasure] )

 

I tried that equation but my grand total is wrong.

 

average_soo = AVERAGE(CONTROL_TOWER_TIER1_TIER2_SUMMARY_FACT[STORE_OOS])
average_ssi = AVERAGE(CONTROL_TOWER_TIER1_TIER2_SUMMARY_FACT[STORE_IN_STOCK])

SumAvgSIS = SUMX(VALUES(MATERIAL_DIM[MATERIAL]),[average_ssi])
SumAvgSOO = SUMX(VALUES(MATERIAL_DIM[MATERIAL]),[average_soo])

jlivenere25_0-1675123117788.png

 

Searched a lot for this answer, thanks for it!

Hi Alexis,

 

Thanks a lot. It works fine

😀

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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