Forum Discussion

jadg's avatar
jadg
Regular Visitor
1 year ago

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

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

 

HPVAToolU
IAPP1V1A1051
IAPP1V1A1071
IAPP1V1A1081
IAPP1V1A1091
IAPP1V1A2051
IAPP1V1A2071

 

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.

8 Replies

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

     

     

    • jadg's avatar
      jadg
      Regular Visitor

      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's avatar
        lbendlin
        Icon for Super User rankSuper User

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