Matrix sum at lower level and Average at Higher level

[dhanekula1996]

Hello all,

I am Trying to sum the values at lower Hierarchy (Product A = 10) which inturn is the summed value.( 10 = 2+3+5). Total is the sum of values across product A (Total = New +Inprogress+..). I want to show the Average value of that type in higher hierearchy.( 13.33 = (10+20+10) /3).  How show this calculation in Matrix Visualization.

Thanks for your help,

Shashi

Type\Status		New	Inprogress	Pending	Completed	Total
10010		13.33333333	3.333333333	0	13.333333	30
	Product A	10	0	0	10	20
	Product B	20	10	0	20	50
	Product C	10	0	0	10	20
10011		20	15	5	0	35
	Product D	10	10	0	0	20
	Product E	20	20	10	0	50

[Jihwan_Kim · Accepted solution]

Hi, @dhanekula1996 

Please check the below picture and the sample pbix file's link down below.

I suggest having a table structure and data model like below.

All measures are in the sample pbix file.

https://www.dropbox.com/s/40f5vyps3bqxayn/dhanekula.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[dhanekula1996]

Sir, Thank you so much for the response, It is working but in Higher level Hierarchy  currently it is showing the Average Total is 

 Sum of total number of total for each product / ( number of columns * number of rows )  --wrong

 Sum of total number of total for each product / ( number of columns )-- correct

*Blanks are not counted.

[Jihwan_Kim]

Hi, @dhanekula1996 

Thank you for your feedback.

Please share your sample pbix file's link here, then it will be much helpful not to create any gaps between I use sample A and you use sample B.

[dhanekula1996]

Thank you so much for your help sir. I figured the problem and fixed it.

[amitchandak]

@dhanekula1996 , assume you have measure [new], you have create and use a measure like

new measure = averageX(values(Table[product]), [new])

[dhanekula1996]

My apologies sir, Actually the columns New, Inprogress.. are in values of Status column. 

So shall I create a Individual column for each category (New, Inprogress...) and create a mentioned measure?