Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsRank the table, in a decendent way, based on the value you require. Use the calculate function to mean filtering for the first 15.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThis measure worked for my requirements. Thanks a lot 😊
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |