Add a % column to matrix visual

[DeveshSeth]

Hello friends

What I want to establish is probably best explained by an example.

Let's say I have the following table:

Toy	Good/Bad
Abacus	Good
Abacus	Bad
Abacus	Bad
Abacus	Bad
Puzzle	Good
Puzzle	Bad
Puzzle	Bad
Doll	Good
Doll	Good
Doll	Good

I do a matrix visual in PBI with toys as row labels and good/bas as columns:

Row Labels	Bad	Good	Grand Total
Abacus	3	1	4
Doll		3	3
Puzzle	2	1	3
Grand Total	5	5	10

I want to add another column (see below):

Row Labels	Bad	Good	Grand Total	Percentage of Good Toys
Abacus	3	1	4	25%
Doll		3	3	100%
Puzzle	2	1	3	33%
Grand Total	5	5	10	50%

What's the best way to achieve this?

Many thanks in advance.

Devesh

[parry2k]

@DeveshSeth here is the measure

% Good = DIVIDE ( CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Good/Bad] = "Good" ), COUNTROWS ( 'Table' ) )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[DeveshSeth]

@parry2k Thanks, I think we're close but how do I use the measure in the matrix visual to obtain the table (with % good column) I provided in my question. When I add it to the matrix visual I get the attached table - with extra columns that are not wanted.

The table columns should look like this:

Row Labels	Bad	Good	Grand Total	Percentage of Good Toys
Abacus	3	1	4	25%
Doll		3	3	100%
Puzzle	2	1	3	33%
Grand Total	5	5	10	100%

[Ashish_Mathur]

Hi,

This is not a method i'd prefer to use but i had to.  You may download my PBI file from here.

Hope this helps.

[DeveshSeth]

Hi Ashish

Thanks, but your solution is starting with a pivot of the original table and hence it is possible to visualize via a 'Table' visual. 

How do I start with the table posted in my original question and still achieve the intended solution?

Thanks

Devesh

[DeveshSeth]

Sorry I see the pivot step in M query now. 

[Ashish_Mathur]

Hi,

If my reply helped, please mark it as Answer.

[DeveshSeth]

Thanks again Ashish. For many reasons, I wanted the solution to use the matrix visualization with the columns mentioned. Specially because the table has a lot of other columns/processed data and is used for multiple other reports/dashboards - hence it won't be possible to pivot in M unless am doing that for only this visualization separately. 

The data I used in a simple stylized example to explain the problem.

[Ashish_Mathur]

Hi,

Here's another bad method, yet a method.  You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur]

You are welcome.  In which case, i dno't think you will get a perfect solution to this problem.

[parry2k]

@DeveshSeth do you have only two categories good and bad, if yes then you can add following measures, and without doing any transformation, you can use table/matrix visual

Total = COUNTROWS( Table )

Good = CALCULATE( [Total], Table[Good/Bad] = "Good" )

Bad = CALCULATE( [Total], Table[Good/Bad] = "Bad" )

use the above measures in  your visual

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation! ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[DeveshSeth]

But the question still remains - how do I get the percentage column in my matrix visual? 

[parry2k]

@DeveshSeth you can use the same % calculation which I provided before, nothing changed on that. You should be able to take it from here.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation! ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[DeveshSeth]

Unfortunately, putting % good column in matrix visual gives me this:

I have put Toys as row label and Good/bad as columns, value field has 'Count of Good Bad' and '%Good". Could you tell me what are you putting in your matrix visual to give you the result as outlined in my original question?

[parry2k · Accepted solution]

@DeveshSeth you don't' need to put good/bad on columns, just 4 measures that are created.

[DeveshSeth]

Bingo. Magic! 

Many thanks for the solution and also for bearing with my naivety. 

[DeveshSeth]

As you probably said, this wouldn't be a preferred solution. In my case, the table itself has multiple columns and many other processing step and Ideally I wouldn't want to pivot the whole data in M query. The pivoting step is preferred last (as a matrix visual) after data transformation, adding business logic (including in M and DAX) is done.