Forum Discussion
Calculating % category using same dynamic sum as denominator
I am trying to figure out how to create a % for a category using the same denominator across all rows
Table 1 has the calculated measure that is a summation of the No Health variable (e.g. the denominator for category %)
Total= Calculate(Sum('Table 1'[No Health])
| Entry ID | Type | No Health |
| 1 | Public | 1 |
| 2 | Business | 1 |
| 3 | Public | 1 |
| 4 | Health | 0 |
| 5 | Health | 0 |
| 6 | Business | 1 |
Next, I need to get a count (Entry ID) in Table 2 for each category
ID_COUNT= CALCULATE(COUNT('Table 2'[Entry ID]))
| Entry ID | Category |
| 1 | A |
| 1 | B |
| 1 | C |
| 2 | B |
| 2 | C |
| 3 | A |
| 3 | C |
| 4 | B |
| 4 | C |
| 5 | A |
| 5 | C |
| 6 | B |
| 6 | C |
Finally, I need to be able to use the numerators and denominator to create a % by category (Table 3)
% Category= CALCULATE(DIVIDE([Total,[ID_Count], ALL('Table 2'[Category])))
| Category | Count | % Category |
| A | 2 | 50% |
| B | 3 | 75% |
| C | 4 | 100% |
| D | 0 | 0% |
Hi , lisago1978
Just need to change your measure as below:
% Category = CALCULATE(DIVIDE([ID_Count],[Total]))Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
lisago1978
Can you share the tables where I can see a clear relationship?
And, explain how you calculated the following please:Count and % Category in the last table
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
I accept KUDOS 🙂
Hi , lisago1978
Just need to change your measure as below:
% Category = CALCULATE(DIVIDE([ID_Count],[Total]))Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
