cancel
Showing results for
Did you mean:
Microsoft

## Percentage of total by one category in a Matrix as a new column

Hello community,

After some research and tries on my own I haven't been able to fully solve this issue. The problem statement with dummy data is as follows:

I have a variable called "Area" which defines the territory where the car was sold. Another variable is telling me if the car was electric or not. So my dataset would look like this:

 Area Category Europe Electric Europe Non-Electric America Electric Asia Non-Electric Europe Non-Electric Europe Electric Europe Non-Electric

I am trying to create a matrix which represents the areas as rows, on column will be the categorical variable and then the count of each category as values; for example, for Europe it will look like this:

 Area Electric Non-Electric Europe 2 3

I want to calculate a new measure that simply shows me the ratio between electric cars and total of cars by Area; in this case, for europe, the new column will show 40%, which is 2/ (2+3); each measure I attempted created extra more columns, not just one that would calculate this ratio.

Thank you.

1 ACCEPTED SOLUTION
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
4 REPLIES 4
Solution Sage

Cars sold =
COUNTROWS[tablename]

Electric cars sold =
CALCULATE(
[Cars sold],
Category = "Electric"
)

Electric % =

DIVIDE(
[Electric cars sold],
[Cars sold]
)

Then format Electric % as a percentatge to 2 decimals

Microsoft

Hey, thank you for your input. This unfortunately has the same result: it creates a new column for each of the category; I need just one column that has the ratio.

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

If you don't want the category, can't you just remove it from the columns field of your matrix?

@speedramps has the answer. Just put those three measures (or whatever measures you want) on your table without category on the columns.

Announcements

#### Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors