Calculate average value in a matrix hierarchy

[commonsenseuser]

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.

[Guiseppe]

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

[MFelix · Accepted solution]

Hi @commonsenseuser ,

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.

[commonsenseuser]

This measure worked for my requirements. Thanks a lot 😊