Skip to main content
cancel
Showing results for 
Search instead 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

Reply

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.

 

sales.PNG

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
MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
Guiseppe
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.

MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



This measure worked for my requirements. Thanks a lot 😊

Helpful resources

Announcements
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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