Avg not adjusting to context in matrix (need help with summarize function likely)

[Anonymous]

Hi all,

I am trying to take an average of a filtered data set, and have that average properly adjust to the context of a matrix. In other words, the average I am trying to calculate should be row based, as in the average of all rows, properly adjusting to each grouping in the matrix. For example, here are the functions I have tried using, both with the same (wrong) result:

BookingAvg = AVERAGEX( FILTER('booking_table',VALUE('booking_table'[booking_year]) = 2021), 'booking_table'[bookings])

BookingAvg = var filtered1 = FILTER('booking_table', VALUE('booking_table'[booking_year]) = 2021) RETURN

    CALCULATE ( AVERAGE('booking_table'[bookings]), filtered1 )

This lead to the following:

As you can see, the average is often repeated among columns, and when it's not repeated, it's a multiple based on how many subcategories there are. What's happening seems to be a high level average after the grouping, instead of a row based average within each grouping, which would be much lower.

From searching online, it seems I need to incorporate summarize or summarizex into my formulas above somehow, but I am not sure how to do it properly. Can anyone help here? With any examples of summarize functions that might have groupings incorporated?

Note that I really want to solve this within a dax function, as opposed to simply taking an avg calculation within the matrix values field. This would help give me flexibility to do everything I need.

Any help would be great. Thank you.

[Ashish_Mathur]

Hi,

It would depend upon the relationships and the existence of a Calendar Table.  To get ore help, share the link from where i can download your PBI file and show the expected result.

[Anonymous]

Hi,

Thank you for your reply. Is there a common way to provide PBI reports here? Other than providing my own source link? Doesn't seem like I can attach it.

[Ashish_Mathur]

Upload the PBI to Google Drive/One Drive and share the download link.

[Anonymous]

also, why is a calendar table relevant here? my year column is actually text based, so I would want this to work for any kind of filter, not just one based on date

[Anonymous]

Hi, thank you for your reply.

what do you mean remove the filter? I need the filter to be part of the formula, it's required for my unique use case

If I simply throw my data into a matrix, set values to average, and add a basic filter for 2021, it will properly calculate the average within each grouping. But that's not what is happening here. I blocked out the headers for privacy, but the point would be that each grouping would have its own individual average (avg for the same field within each grouping).

[HoangHugo]

Hi

the matrix will summarize, it mean SUM all row your table. Example, formua you want Avg = (number 1 + number 2 + number 3)/3, but when you put it in matrix, it will be Avt = (SUM(number 1+2+3))/1. So please try the revised measure:

BookingAvg = AVERAGEX( CALCULATETABLE('booking_table',ALL(row of matrix),VALUE('booking_table'[booking_year]) = 2021), 'booking_table'[bookings])

[Anonymous · Accepted solution]

thanks for this, I ended up using summarize to create a new set, then put my calcs on top of it and it worked properly

thank you for an additionl solution though

[HoangHugo]

Hi,

I don't know what is row, column header in your matrix, it' hidden. But when you put your data into Matrix context. It will be summarized. So, If you want make Avarage measure, you should remove appropriate filters of Matrix.