Forum Discussion
Matrix with single column value and percentage
This is my Table structure.
| Unique id | report date | Status |
| 1 | 1-Jan-2024 | Closed |
| 2 | 1-Jan-2024 | Closed |
| 3 | 1-Jan-2024 | Not Closed |
| 4 | 1-Jan-2024 | Not Closed |
| 1 | 1-Feb-2024 | Closed |
| 2 | 1-Feb-2024 | Closed |
| 3 | 1-Feb-2024 | Closed |
| 4 | 1-Feb-2024 | Not Closed |
| 1 | 1-Mar-2024 | Closed |
| 2 | 1-Mar-2024 | Closed |
| 3 | 1-Mar-2024 | Closed |
| 4 | 1-Mar-2024 | Closed |
I want my Matrix Visual show be like this .
| Month | Closed | Not Closed | Grand Total | Closed ( % ) |
| Jan | 3 | 7 | 10 | 30% |
| Feb | 6 | 4 | 10 | 60% |
| Mar | 10 | 10 | 100% | |
| Grand Total | 19 | 11 | 30 | 63% |
I tried with Measure for Closed ( % ) in Values, It is not listed as a single row along with Closed and not Closed Data.
Need help.
Hi lm_suresh
You can get the desired result with measures :
Closed = CALCULATE(COUNTROWS('Table'), 'Table'[Status] = "Closed") Not Closed = CALCULATE(COUNTROWS('Table'), 'Table'[Status] = "Not Closed") Total = CALCULATE(COUNTROWS('Table'), ALL('Table'[Status])) % closed = DIVIDE([Closed], [Total])Note that the numbers are different from yours because your table is not the same as the one shown in the example. For instance, in January there aren't 9 cases.
+
Note: The numbers differ from yours because your table is not the same as the one shown in the example — for instance, there aren’t 9 cases in January.
Additionally, from a UX and effective presentation standpoint, displaying the months horizontally is easier for users to read.
This is because we naturally imagine the timeline flowing from left to right.
Same goes for totals — we’re used to seeing them at the bottom, just like in traditional math exercises 🙂Attaching a file with examples — feel free to adapt it as you see fit!
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
5 Replies
- lbendlin
Super User
Show value as ... percent of row total
You seem to want to show both the number of IDs and the % closed as column totals. That is not something you can do easily in Power BI (multiple totals) - that is more an Excel thing.
- Ritaf1983
Hi lm_suresh
You can get the desired result with measures :
Closed = CALCULATE(COUNTROWS('Table'), 'Table'[Status] = "Closed") Not Closed = CALCULATE(COUNTROWS('Table'), 'Table'[Status] = "Not Closed") Total = CALCULATE(COUNTROWS('Table'), ALL('Table'[Status])) % closed = DIVIDE([Closed], [Total])Note that the numbers are different from yours because your table is not the same as the one shown in the example. For instance, in January there aren't 9 cases.
+
Note: The numbers differ from yours because your table is not the same as the one shown in the example — for instance, there aren’t 9 cases in January.
Additionally, from a UX and effective presentation standpoint, displaying the months horizontally is easier for users to read.
This is because we naturally imagine the timeline flowing from left to right.
Same goes for totals — we’re used to seeing them at the bottom, just like in traditional math exercises 🙂Attaching a file with examples — feel free to adapt it as you see fit!
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
v-menakakotaCommunity Support
Hi lm_suresh ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
- v-menakakota
Hi lm_suresh ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.- v-menakakota
Hi lm_suresh ,
I hope the provided information is helpful. Thank you lbendlin for providing possible solution. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
