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
Daniel_Derbin
Frequent Visitor

AverageX subtotals are SUM instead of Average in Matrix.

Hi, 

 

I'm using below Measure to calculate Averages on hosts, and that works fine on day level, however when using date hierarchy in Matrix the subtotals are suming up the results instead of showing average for weeks, months etc.
Any ideas why?

Open Average on host =
CALCULATE(
    AVERAGEX(VALUES(TSCDB_Prod_BACKUP[Host Name]),[Open]))
Daniel_Derbin_0-1664364645688.png

 

 
1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your datamodel looks like, but please try writing something like below whether it works.

 

Open Average on host =
AVERAGEX (
    SUMMARIZE ( 'FactTableName', TSCDB_Prod_BACKUP[Host Name], 'Calendar'[Date] ),
    [Open]
)

  

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your datamodel looks like, but please try writing something like below whether it works.

 

Open Average on host =
AVERAGEX (
    SUMMARIZE ( 'FactTableName', TSCDB_Prod_BACKUP[Host Name], 'Calendar'[Date] ),
    [Open]
)

  

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Jihwan, 

 

That worked perfectly, thank you!! 🙂

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.

Top Solution Authors