Average by Type and Category using drill down

[Kostas]

Hello Everyone, 

I am having an issue to calculate the total average within a matrix that I created. 

After I searched into the forum I found that I need to use the HASONEFILTER as a way to solve my issue but I am not sure on how ti syntax my measure. 

At the moment I have the following raw data table:

Name	Category	Type	Value
K	Orange		4
L	Orange		6
K	Apple	Green	8
K	Apple	Red	12
L	Pear		14
L	Apple	Red	10

For the purposes of my analysis I created a matrix table that shows the average number of the value column by using in the rows the name and in the columns the Category and with drill down option to see the type as seems below:

Name	Orange	Apple	Pear	Total
K	4	10 [ (8+12) / 2 ]		8 [ ( 4+ 8+12 ) / 3]
L	6	10	14	10

The problem is that the total column instead of producing the average total of the categories it takes into account also the types underneath the category that I do not want to. I need for my matrix or any visuals that I will use to always calculate the total average of the available columns that the user sees instead of taking into account all the data.  Can you please assist as a way to produce the following outcome:

Name	Orange	Apple	Pear	Total
K	4	10 [ (8+12) / 2 ]		7 [ ( 4+ 10 ) / 2 ]
L	6	10	14	10

Thanks for your help in advance

[amitchandak]

@Kostas , These should you measures for table 1 and table 2

1

Avg Value = average(Table[Value])

2

Avg Value 2 = averageX(values(Table[Category]),[Avg Value])

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[Kostas]

Hello, 

Unfortunately that measure returns again the same values. It calculates all the values instead of returning the total only for the values shown in the table. 

I need the total to show the total average per category (e.g. (Average for apples + Average for Oranges) /2 ) and then when the user drill down to columns to see the type again to show the total average for the types. 

Thanks

[amitchandak]

@Kostas , Check if the attached file after the signature can help - Page 2

[Kostas]

Hello, 

The only problem that occurs with the measure is that is not perfectly calculates the total in the columns when drilling down to the type but at least now I can work with those results. 

Thanks for the help 

Kostas