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 am trying to count the number of projects that are meeting a certain goal, there are many projects in which some can belong to multiple cells. Table is layed out similar to below:
lab | project | Funding | expensed |
A | 1 | 100 | 50 |
B | 1 | 200 | 60 |
B | 2 | 300 | 200 |
C | 3 | 200 | 200 |
I have a measure to calculate the Expensed % (Expensed/Funding).
On my Report, there is a slicer where you can look at how each lab is performing, or if no labs are selected than it will look at overall performance. I am wanting to count how many projects are meeting the expensed goal (lets say it is 50%)
My formula is
Meet Goal = calculate( distinctcount(table[project]), filter(table, Expensed % >= to ExpensedGoal)))
The formula works when I have the report filterred to a single lab, but if I am viewing all labs then the count goes wrong. For example it will count project 1 as meeting goal becuase it meets goall under Lab A, even though project 1 does not meet goal when you combine Lab A and B's performance.
How can I have the count work accurately for when all labs are selected?
Solved! Go to Solution.
FYI, got it to work by creating a summarized table by the projects. I then adjusted my meet goal formula with :
if ( hasonevalue(table[lab], calculate( distinctcount(table[project]), filter(table, table[Expensed %] >= to ExpensedGoal))), calculate( distinctcount(table[project]), filter(calculatedtable, calculatedtable[Expensed %] >= to ExpensedGoal)))
definately not the best way to go about it, but is my work around until I get something else working.
the shorter way to answer this is to create a new calculated column (not a measure) in your table to for Expenses %
then your "Meet Goal" measure should work
the problem you are facing is related to row context operations, the way you were doing it is not allowing for the measure Expenses % to calculate row by row
If I answered your question please accept the reply as the solution 🙂
It still seems to be counting the same. I created the collumn and then changed the formula to:
Meet Goal = calculate( distinctcount(table[project]), filter(table, table[Expensed %] >= to ExpensedGoal)))
When I click the slicer for an individual lab it counts correctly, it is only when all are selected that it seems to count a project as long as the goal is met in at least one of the associated labs
FYI, got it to work by creating a summarized table by the projects. I then adjusted my meet goal formula with :
if ( hasonevalue(table[lab], calculate( distinctcount(table[project]), filter(table, table[Expensed %] >= to ExpensedGoal))), calculate( distinctcount(table[project]), filter(calculatedtable, calculatedtable[Expensed %] >= to ExpensedGoal)))
definately not the best way to go about it, but is my work around until I get something else working.
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 |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
124 | |
112 | |
98 | |
81 | |
72 |