Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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.
Your sample data doesn't seem to cover your scenario. Is this what you are looking for?
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.
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
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.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
72 | |
62 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
62 | |
59 | |
56 |