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 a...
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.

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.