cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Calculate average value in a matrix hierarchy

Hi all,

I have a matrix which shows March Sales % and Jan-Mar Sales % in a column. There are 3 headers in the matrix: Top 15 Products, Sales Growing Products and Others. I want to calculate the average of those top 15 products which have March Sales % greater than 100 i.e. the cells which are colored blue and show the average in Sales Growing Customers row. The average value will be placed under the columns shown in the green box for both March Sales % and Jan-Mar Sales %.  I have attached an image to illustrate this below.

I have tried some approaches but they keep comparing the total value of 121% in the Top 15 row and giving wrong results. I need to know how we can iterate over the products under the Top 15 header and compare if greater than 100%.

I hope I have explained my problem properly. If you have any questions, feel free to ask.

Any help is appreciated. Thanks.

1 ACCEPTED SOLUTION
Super User

You need to create a measure similar to this one:

Measre average on totla =
IF (
HASONEVALUE ( Table[Product] );
[March Sales %];
AVERAGEX (
FILTER (
SUMMARIZE ( 'Table'; 'Table'[Product]; "@Sales_percentage"; [March Sales %] );
[@Sales_percentage] > 1
);
[March Sales %]
)
)

Be aware that this measure may need adjustments.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

3 REPLIES 3
New Member

Rank the table, in a decendent way, based on the value you require. Use the calculate function to mean filtering for the first 15.

Super User

You need to create a measure similar to this one:

Measre average on totla =
IF (
HASONEVALUE ( Table[Product] );
[March Sales %];
AVERAGEX (
FILTER (
SUMMARIZE ( 'Table'; 'Table'[Product]; "@Sales_percentage"; [March Sales %] );
[@Sales_percentage] > 1
);
[March Sales %]
)
)

Be aware that this measure may need adjustments.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper I

This measure worked for my requirements. Thanks a lot 😊

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors