Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a matrix and I need to calculate the average of rows in the matrix for the past 12 months.
The average for 'Actual Exp' will be different than the 'Actual Min' values, and RAG will be calculated based on the average value of 'Actual Exp'.
I don't know how to get the average for 'Actual Exp' and 'Actual Min' in a matrix.
Thanks, guys.
Solved! Go to Solution.
To calculate the separate averages for Actual Exp and Actual Min you can create a table with measures. The formula for the measure is:
Avg_Actual Exp = AVERAGE({Table Name}, {Column Name})
This formula will also consider only the valid values and will omit the text or null values.
For those values which only have null or text values in the rows, the formula is:
Avg_Actual Exp = IF(ISNUMBER(AVERAGEX({Table Name}, {Column Name}), AVERAGEX({Table Name}, {Column Name},"N/A" )
This will give you the "N/A" if the whole row has null values.
You can replicate the same thing for the Actual Min.
To calculate the separate averages for Actual Exp and Actual Min you can create a table with measures. The formula for the measure is:
Avg_Actual Exp = AVERAGE({Table Name}, {Column Name})
This formula will also consider only the valid values and will omit the text or null values.
For those values which only have null or text values in the rows, the formula is:
Avg_Actual Exp = IF(ISNUMBER(AVERAGEX({Table Name}, {Column Name}), AVERAGEX({Table Name}, {Column Name},"N/A" )
This will give you the "N/A" if the whole row has null values.
You can replicate the same thing for the Actual Min.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |