Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi, I've only been using PowerBI for the last few weeks and have been asked to look at an existing dashboard. Below is an image that shows a matrix in their dashboard. Currently it appears there is a subtotal for each Program that appears to just be an average of all the teams under that program. For example, Corp Information and Technolo... has 6 teams with values of 80, 52, 73, 82, 79 and 82 and a Team Score column. The subtotal then shows a value of 75 which is the average. Team Stability is another column that shows as a %. I'm wondering if you can have unique formulas for each column versus doing an average for all. For example, can I have an average for the %'s but then have a sum (or other formula) for the other values? Is this possible and if so how is that accomplished?
Regards,
Solved! Go to Solution.
Hi @plaplant ,
Yes! You can do some steps as follows.
1. For some simple senarios (sum or average of total table), you can change the type of aggregation in each field.
2. You can also choose the percentage of values in "Show value as".
3. For some special senarios, just like "I would like to show the average of each category", you can create some measures. I did a test. The result is here.
TEST_1 =
IF (
ISINSCOPE ( Sheet2[Category] ),
DIVIDE (
SUMX (
FILTER ( ALL ( Sheet2 ), [Category] = SELECTEDVALUE ( Sheet2[Category] ) ),
[Value]
),
SUMX (
FILTER ( ALL ( 'Sheet2' ), [C2] = SELECTEDVALUE ( Sheet2[C2] ) ),
[Value]
)
),
DIVIDE (
SUMX ( FILTER ( ALL ( Sheet2 ), [C2] = SELECTEDVALUE ( Sheet2[C2] ) ), [Value] ),
CALCULATE ( SUM ( 'Sheet2'[Value] ), ALL () )
)
)
TEST_2 = AVERAGE(Sheet2[Value])
Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please disregard this part of the question. I found that I can simply use
Hi @plaplant ,
Yes! You can do some steps as follows.
1. For some simple senarios (sum or average of total table), you can change the type of aggregation in each field.
2. You can also choose the percentage of values in "Show value as".
3. For some special senarios, just like "I would like to show the average of each category", you can create some measures. I did a test. The result is here.
TEST_1 =
IF (
ISINSCOPE ( Sheet2[Category] ),
DIVIDE (
SUMX (
FILTER ( ALL ( Sheet2 ), [Category] = SELECTEDVALUE ( Sheet2[Category] ) ),
[Value]
),
SUMX (
FILTER ( ALL ( 'Sheet2' ), [C2] = SELECTEDVALUE ( Sheet2[C2] ) ),
[Value]
)
),
DIVIDE (
SUMX ( FILTER ( ALL ( Sheet2 ), [C2] = SELECTEDVALUE ( Sheet2[C2] ) ), [Value] ),
CALCULATE ( SUM ( 'Sheet2'[Value] ), ALL () )
)
)
TEST_2 = AVERAGE(Sheet2[Value])
Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you, I will review this in more detail and test out but was curious on something else as well. If, in my matrix, I have a field that is just notes imported from a spreadsheet, how can I display the notes but then eliminate the total JUST for this one column. The other columns would be the sum's or the averages but a total doesn't make sense with a note column like this. I'm wondering how I can simply bring in the notes without a total being displayed as the matrix tries to apply something there.
Please disregard this part of the question. I found that I can simply use
Hi @plaplant ,
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem.
Best Regards,
Yuna
@plaplant , Isinscope and value check can help
refer:https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
and https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/
or
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
108 | |
108 | |
93 | |
61 |
User | Count |
---|---|
169 | |
138 | |
135 | |
102 | |
86 |