cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alenic235
Microsoft
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:

 

AreaCategory
EuropeElectric
EuropeNon-Electric
AmericaElectric
AsiaNon-Electric
EuropeNon-Electric
EuropeElectric
EuropeNon-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:

AreaElectricNon-Electric
Europe23

 

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

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
speedramps
Solution Sage
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


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.

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors