Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply

Calculate average value of a matrix column in hierarchical view

Hi,

I have a matrix as follows which shows the sales of top 15 products and the past one month (March 2020) and past 3 months (Jan-Mar) values in percentages. I would like to average the values greater than 100% by checking the individual values under the top 15 header. So in the photo below all values above 100% (which are blue) would be added and divided by 13 (all blue values except red) in case of March Sales % and same idea for Jan-Mar Sales %. I tried some formulas but it gives me the wrong results in Sales Growing Products (139% and 129.6%, see below). How can I go about solving this problem?

 

forum.PNG

All the values are composed of measures like March Sales %, Jan-Mar Sales % etc. The measures used are:

 

March Sales % Growing = IF([March Sales % Values Top 15] > 1, AVERAGEX(Table1, [March Sales % Values Top 15]))
 
March Sales % Growing Total =
IF ( NOT ( ISFILTERED ( Table1[Top_Product_Level] ) ), [March Sales % Growing])
 
March Sales % Values Top 15 =
CALCULATE (
[March Sales %],
KEEPFILTERS (
TOPN ( 15, ALL ( Table1[Top_Product_Level] ), [Sum of March Sales Current Year] )
)
)
 
March Sales % Values Other =
CALCULATE (
[March Sales %],
KEEPFILTERS (
EXCEPT (
ALL ( Table1[Top_Product_Level] ),
TOPN ( 15, ALL ( Table1[Top_Product_Level] ), [Sum of March Sales Current Year] )
)
)
)
 
March Sales % Values Other Total Only =
IF ( NOT ( ISFILTERED ( Table1[Top_Product_Level] ) ), [March Sales % Values Other] )
 
(This measure returns 3 labels: top 15, other and sales growing products. Sales growing products shows value of March Sales % Growing Total)
Top & Other =
IF (
HASONEVALUE ( 'Top & Other'[Top & Other] ),
SWITCH (
VALUES ( 'Top & Other'[Top & Other] ),
"Top 15", [March Sales % Values Top 15],
"Other", [March Sales % Values Other Total Only],
"Sales Growing Products", [March Sales % Growing Total]
),
[March Sales %]
)

Any help will be greatly appreciated.
3 REPLIES 3
jahnavib4
Microsoft Employee
Microsoft Employee

Hi everyone,

I have a requirement similar to the one mentioned above. The data is of 4 Fiscal years I should be publishing a forecast based on the previous years' data. Attaching the sample data for your better understanding. 

 

I have a matrix table built in Power Bi Matrix visual based on certain filters applied like Fiscal Year, Category etc. 
Rows:

Category (Flowers,Fruits etc)

Sub Category (Rose,Tulips,Apple etc)

Column:

Week Number in a quarter (1,2,3...14)

Values:

Sales Revenue.

This matrix is built for Fy18,Fy19,Fy20. I should build the same table for FY 21 by calculating the average of the previous 3 yrs of data.  I'm wondering how to do the same, earlier in Excel it was quite simple task. Since the average of the data is relative to both rows& Columns am confused on how to proceed.     

 

FY18 1234567891011121314
FlowersRose$12M$12M$12M$12M$12M$12M$12M$12M$12M$12M$12M$12M$12M$12M
 

Lilly

$13M$13M$13M$13M$13M$13M$13M$13M$13M$13M$13M$13M$13M$13M
 Daisy$14M$14M$14M$14M$14M$14M$14M$14M$14M$14M$14M$14M$14M$14M
 Tulip$15M$15M$15M$15M$15M$15M$15M$15M$15M$15M$15M$15M$15M$15M
Fruits

Mango

$22M$22M$22M$22M$22M$22M$22M$22M$22M$22M$22M$22M$22M$22M
 Apple$27M$27M$27M$27M$27M$27M$27M$27M$27M$27M$27M$27M$27M$27M
 Orange$39M$39M$39M$39M$39M$39M$39M$39M$39M$39M$39M$39M$39M$39M
 Kiwi$44M$44M$44M$44M$44M$44M$44M$44M$44M$44M$44M$44M$44M$44M
v-diye-msft
Community Support
Community Support

Hi @commonsenseuser 

 

you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive for business and share the link to the file. Please do not forget to describe the expected results based on this sample data.

 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Hi @v-diye-msft ,

 

Thanks for the reply. I know that I might not have explained the problem with much clarity. As suggested, I have created a sample pbix file so that you can take a look to better understand the problem.

Link to sample pbix: https://1drv.ms/u/s!AjhtAEoJ85hKoxfCHQ8fZa4yDYSr?e=XmV8L7

 

I have also explained the problem and the expected solution a bit more in the sample file accordingly.

 

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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