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.
I have dug around the forums and have a general idea of what I should be doing to accomplish this, but I'm missing something, and hoping the hive mind can help.
I am trying to show the company average of a value on a card. I get the value in a table, and the table shows the average correctly when filtering by date, etc.
I need to get that average on a card.
Exmaple of table with average:
I created a measure as follows:
---
CompanyAveragePR =
CALCULATE(
DIVIDE(
SUMX(FILTER(PerformanceRatio, PerformanceRatio[PerformanceRatio] <> 0),PerformanceRatio[PerformanceRatio]),
COUNTX(FILTER(PerformanceRatio, PerformanceRatio[Total] <> 0), PerformanceRatio[ReportedDate]),
0),
ALLSELECTED(PerformanceRatio),
PerformanceRatio[Total] <> 0,
PerformanceRatio[PerformanceRatio] <> 0
)
---
But, as noted in the in the above table, it's coming up with the wrong result, apparently taking everything into account.
Where am I going wrong?
Thanks,
Dion
Solved! Go to Solution.
@mahoneypat , @v-eachen-msft : Thank you for your replies.
Unfortunately neither result matched the Avg PR from the table, but in the course of working through your suggestions I think I found a result that works, using 3 Measures:
PRTotal = SUM(PerformanceRatio[PerformanceRatio])
DaysWorkedCount = COUNTA(PerformanceRatio[ReportedDate])
CompanyAverage =
CALCULATE(
[PRTotal]/[DaysWorkedCount],
ALLSELECTED(PerformanceRatio))
This matches the Avg PR displayed in the table, and adjusts along with the table based on the selected dates, people, etc. which it what I needed for the card.
Thank you.
Hi @addaline ,
Do you mean to get a measure which is like "Avg PR"?
You could use ALLEXCEPT() to get result of each name:
CompanyAveragePR =
CALCULATE (
DIVIDE (
SUMX (
FILTER ( PerformanceRatio, PerformanceRatio[PerformanceRatio] <> 0 ),
PerformanceRatio[PerformanceRatio]
),
COUNTX (
FILTER ( PerformanceRatio, PerformanceRatio[Total] <> 0 ),
PerformanceRatio[ReportedDate]
),
0
),
ALLEXCEPT ( PerformanceRatio, PerformanceRatio[CrewLeaderName] ),
PerformanceRatio[Total] <> 0,
PerformanceRatio[PerformanceRatio] <> 0
)
@mahoneypat , @v-eachen-msft : Thank you for your replies.
Unfortunately neither result matched the Avg PR from the table, but in the course of working through your suggestions I think I found a result that works, using 3 Measures:
PRTotal = SUM(PerformanceRatio[PerformanceRatio])
DaysWorkedCount = COUNTA(PerformanceRatio[ReportedDate])
CompanyAverage =
CALCULATE(
[PRTotal]/[DaysWorkedCount],
ALLSELECTED(PerformanceRatio))
This matches the Avg PR displayed in the table, and adjusts along with the table based on the selected dates, people, etc. which it what I needed for the card.
Thank you.
Hi @addaline ,
I am glad that you could find the solution. You could accept your own reply to close the topic.
Not sure I fully understand your issue but you could try a pattern like this to get your desired result. I can't tell if the P.R. and Days Worked columns are columns or measures so here are both version.
If columns
CompanyAverage = SUMX(VALUES(Table[CrewLeaderName]), CALCULATE(DIVIDE(SUM(Table[P.R.]), SUM(Table[Days Worked]))))
If measures
CompanyAverage = SUMX(VALUES(Table[CrewLeaderName]), DIVIDE([P.R.], [Days Worked]))
You can wrap the whole thing in CALCULATE( ... , ... <> 0, ...<>0) if needed.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |