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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
jadg
Regular Visitor

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 8
lbendlin
Super User
Super User

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

 

lbendlin_0-1738268646569.png

 

Thanks for your reply Ibendin

See below in Excel what I am looking for:

 

jadg_1-1738314236138.png

 

 

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.

 

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

Hi @lbendlin ,

Also see the implementation in PBI

 

jadg_0-1738685043687.png

 

 

Thanks again for your support

Are you showing the expected result, or something else?

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

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

Hi @lbendlin 

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

HPVAToolU
IAPP1V1A101100%
IAPP1V1A102100%
IAPP1V1A10380%
IAPP1V1A10450%
IAPP1V1A10530%
IAPP1V1A10680%
IAPP1V1A10750%
IAPP1V1A10880%
IAPP1V1A20160%
IAPP1V1A20280%
IAPP1V1A20350%
IAPP1V1A20530%
IAPP1V1A20750%
IAPP1V2A30150%
IAPP1V2A30440%
IAPP1V2A30820%
IAPP1V2A40380%
IAPP1V2A406100%
IAPP2V3A50110%
IAPP2V3A50320%
IAPP2V3A50430%
IAPP2V3A50550%
IAPP2V3A50730%
IAPP2V3A51050%
IAPP2V3A60160%
IAPP2V3A60350%
IAPP2V3A60530%
IAPP2V4A70150%
IAPP2V4A70440%
IAPP2V4A70820%
IAPP2V4A80380%
IAPP2V4A806100%

 

Thanks for your support

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

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!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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