Forum Discussion

commonsenseuser's avatar
commonsenseuser
Icon for Helper I rankHelper I
6 years ago
Solved

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.

Need Help
Tips and Tricks
  • MFelix's avatar
    MFelix
    6 years ago

    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.

3 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    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.

  • Guiseppe's avatar
    Guiseppe
    New Member
    6 years ago

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