How to calculate averages in a Matrix visual using INSCOPE for different groups?

[jadg]

Hi, 
I am attempting to fill a matrix based on a table shared here.

H	P	V	A	Tool	U
I	APP1	V1	A1	05	1
I	APP1	V1	A1	07	1
I	APP1	V1	A1	08	1
I	APP1	V1	A1	09	1
I	APP1	V1	A2	05	1
I	APP1	V1	A2	07	1

This Matrix is representing a % of usage (Field U) of tools (Field Tool) used per Applications (Field A). These Applications are grouped per V, also per P and H.

Until now, I am able to represent this information in a Matrix. However the challenge I am facing is to calculate the average of usage in the grouping rows (V, P and H). Meaning that I have to have rows per A, here the values are shown without calculation, then per V here the average is calculated ok. But then I have to show the row per P. P must be the average of the V rows contained and already calculated. For H I have to see the average per P rows contained and already calculated. So far, for P I am getting for P the average of the column but not at V level but at A level so the result is not ok. Another challenge I have is the total column, I need this column is calculated with the average of each row considering the respective level. Below is the measure I am using:

Measure

---------

U =
VAR AveragePerToolInV =
AVERAGEX(VALUES('Table'[Tool]),[AR_M])
VAR AveragePerV =AVERAGEX(VALUES('Table'[V]),AveragePerToolInV)
VAR AveragePerP =AVERAGEX(VALUES('Table'[P]),AveragePerV)
VAR AverageForPRow =AVERAGEX(VALUES('Table'[P]),AveragePerV)
VAR AveragePerH =AVERAGEX(VALUES('Table'[P]),AverageForPRow)
VAR AverageForHRow =AVERAGEX(VALUES('Table'[T]),AveragePerP)
RETURN
SWITCH(TRUE(),
ISINSCOPE('Table'[A]),[AR_M],
ISINSCOPE('Table'[V]),AveragePerToolInV,
ISINSCOPE('Table'[P]),AverageForPRow,
ISINSCOPE('Table'[H]),AverageForHRow,
// Default case for overall total
AVERAGEX(VALUES('Table'[H]),AverageForHRow)
)
[AR_M] is another measure AR_M = AVERAGE('Table'[UU])

I would really appreciate your support.

[lbendlin]

Your sample data doesn't seem to cover your scenario.  Is this what you are looking for?

[jadg]

Thanks for your reply Ibendin

See below in Excel what I am looking for:

In PowerBI the Data is being displayed this way but the problem is with the results I am getting in the rows for H and P.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[jadg]

Hi @lbendlin ,

Also see the implementation in PBI

Thanks again for your support

[lbendlin]

Are you showing the expected result, or something else?

[jadg]

No, I am showing the PBI report that it is not fullfilling the requirement described in the initial email.

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[jadg]

Hi @lbendlin 

Below is the table that can be use to represent the outcome I showed before.

H	P	V	A	Tool	U
I	APP1	V1	A1	01	100%
I	APP1	V1	A1	02	100%
I	APP1	V1	A1	03	80%
I	APP1	V1	A1	04	50%
I	APP1	V1	A1	05	30%
I	APP1	V1	A1	06	80%
I	APP1	V1	A1	07	50%
I	APP1	V1	A1	08	80%
I	APP1	V1	A2	01	60%
I	APP1	V1	A2	02	80%
I	APP1	V1	A2	03	50%
I	APP1	V1	A2	05	30%
I	APP1	V1	A2	07	50%
I	APP1	V2	A3	01	50%
I	APP1	V2	A3	04	40%
I	APP1	V2	A3	08	20%
I	APP1	V2	A4	03	80%
I	APP1	V2	A4	06	100%
I	APP2	V3	A5	01	10%
I	APP2	V3	A5	03	20%
I	APP2	V3	A5	04	30%
I	APP2	V3	A5	05	50%
I	APP2	V3	A5	07	30%
I	APP2	V3	A5	10	50%
I	APP2	V3	A6	01	60%
I	APP2	V3	A6	03	50%
I	APP2	V3	A6	05	30%
I	APP2	V4	A7	01	50%
I	APP2	V4	A7	04	40%
I	APP2	V4	A7	08	20%
I	APP2	V4	A8	03	80%
I	APP2	V4	A8	06	100%

Thanks for your support