Percentage of total and value in the same matrix column

[fionabee]

Hello, 

I am trying to build a matrix which shows a ticket statuses in the rows and versions in the column, e.g

Status	Stage 1	Stage 2	Stage 3	Stage 4
Approved
Denied
Pending

and will show the data in each column as a %.

I can achieve this part with no issue but I'd also like to show the total number of tickets in each stage as a value but in the same column eg:

Status	Stage 1	Stage 2
Number	33
Approved	11%
Denied	23%
Pending	66%

This is the part where I get stuck as added the total count of tickets as another value causes it to show in another sub column:

Status	Stage 1
	%	value
Approved	11%	3
Denied	23%	6
Pending	66%	14

Is what I want to do possible or am I using the wrong visual?

[ullassl1991 · Accepted solution]

Hi @fionabee ,

use this option for values in the matrix visual it will be switched to rows and result will come as expected
Thank you

[v-venuppu · Accepted solution]

Hi @fionabee ,

Thank you @lbendlin @ullassl1991 for the Prompt Response.

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-venuppu]

Hi @fionabee ,

I hope this information is helpful. 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 other community members with similar problems can find a solution faster.

Thank you.

[v-venuppu]

Hi @fionabee ,

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 my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[ullassl1991]

Hi @fionabee 
I have achieved the requirement assuming the data structure is like this,

I will explain the steps that I have followed to do this
Step1:create 4 measures for all four status

Number = CALCULATE(COUNT(Sheet1[Status]))
Approved = CALCULATE(COUNT(Sheet1[Status]),Sheet1[Status]="Approved")/[Number]
Pending = CALCULATE(COUNT(Sheet1[Status]),Sheet1[Status]="Pending")/[Number]
Denied = CALCULATE(COUNT(Sheet1[Status]),Sheet1[Status]="Denied")/[Number]

Step2:

Create a indivdual hirarchy table from Enter data option
Step3
Create a field parameter adding all the measures created 

Step4

Create a realtionship between Hir table and Parameter table by joining Measure and Parameter Order fields. Also Create a realtionship between Sheet1[Status] and Hir[Status]
Step5:
Add the fields without rows in matrix visual, for column add Sheet1[Stages] and for value add Parameter Fields

I guess this will solve your problem , let me know 

Thank you

[fionabee]

Hello,

Thank you for this and for the detailed explanation. I've followed these steps and all makes sense but when I add to the matrix, the statuses appear across the top rather than down the left hand side as rows and it seems to have only calculated for one of of the stages?

[ullassl1991 · Accepted solution]

Hi @fionabee ,

use this option for values in the matrix visual it will be switched to rows and result will come as expected
Thank you

[v-venuppu · Accepted solution]

Hi @fionabee ,

Thank you @lbendlin @ullassl1991 for the Prompt Response.

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.

[fionabee · Accepted solution]

Hello, apologies for the delay, I'm just coming back to this project. I've now managed to get everything in the table in the correct order but the data that shows as a percentage in your screenshot shows as decimals for me. Where do I need to change the format to show % instead?

[lbendlin]

Add the value field twice. Change one of them to "Show as... Percentage of column total".

[fionabee]

Hello,

I have tried this as mentioned in my original post:

This is the part where I get stuck as added the total count of tickets as another value causes it to show in another sub column:

Status	Stage 1
	%	value
Approved	11%	3
Denied	23%	6
Pending	66%	14

[lbendlin]

I thought that is your expected outcome.  Seems like you are trying to achieve something different? Based on your sample data, how should the result look like?

[fionabee]

I want the data to look like this:

Status	Stage 1	Stage 2
Number	33
Approved	11%
Denied	23%
Pending	66%

So both the numerical value and the % split within the same column, meaning that there is only one column beneath the title 'Stage 1' rather than it creating two columns beneath 'Stage 1'. 

[lbendlin]

you are moving into UX confusion territory.  You need to make sure your report users understand what you are showing them.