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

Average of measure with Calculate

Dear all,

 

I have this expression: 

 

TAM Amount = CALCULATE(    
         SUM(CifSales[Amount]);
         DATESINPERIOD(CalendarTable[DateKey];LASTDATE(CalendarTable[DateKey]);-12;MONTH)
         )

This, calculate the TAM amount of my sales table (reference date is november 2017). The result its as follows:

TAM AmountTAM AmountWith this, I need to obtain the average value of those 12 records. The correct amount is 11,834,775.87

I have tried several forms of Average and none of them works for me. ¿Can you please help me?

 

Thanks in advance!

 

Best regards,

Antonio.

1 ACCEPTED SOLUTION

AVERAGEX ( SUMMARIZE( CalendarTable;'CalendarTable'[Year]; CalendarTable[ShortMonthName] ); [TAM Amount] )

View solution in original post

7 REPLIES 7
v-chuncz-msft
Community Support
Community Support

@Achamorroman,

 

You may use a similar way as shown here.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AVERAGEX ( SUMMARIZE( CalendarTable;'CalendarTable'[Year]; CalendarTable[ShortMonthName] ); [TAM Amount] )

MFelix
Super User
Super User

Hi @Achamorroman,

 

You need to use an AveregeX expression add another measure like this:

 

Average on total =
IF (
    HASONEVALUE ( Calendar[Years] );
    [TAM Amount];
    AVERAGEX ( ALL ( 'Calendar'[Year]; Calendar[ShortMonthName] ); [TAM Amount] )
)

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix, thanks for fast reply!

 

Unfortunately, that formula doesn't fit what I need. This is the result:

image.png

The line result is the same, but total doesn't work. 

 

Thanks again for your help!

Regards

A.Chamorro 

 

 

Hi again,

 

I found this great article about Rolling 12 Months Average in DAX. I'm going to test it now, it seems to be a solution!

 

Regards!

A.Chamorro. 

Hi @Achamorroman,

 

What result are you expecting?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur,

 

I need one graphic as follows:

 

  • Always 12 months before selected month and year on a slicer.
  • Each month represent the Moving annual totals (MAT).
  • Total of the matrix should be the sum of that 12 months divided by 12

From now, thanks to @mattbrice (its formula works!) I have the right total, but I don't know how can I obtain always those 12 months having a slicer as reference.

 

Any help would be very appreciated

Thanks!

 

 

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.