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
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
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.