cancel
Showing results for
Search instead for
Did you mean:
Helper V

## Get the % of an Item Category

So I'm trying to get the percentage of an item category from my dataset. Let's say I have the following data:

 Name Category Order Date Inventory Apples Fruits 1/1/17 100 Oranges Fruits 1/2/17 70 Grapes Fruits 1/3/17 80 Apples Fruits 1/4/17 20 Oranges Fruits 1/5/17 30

Given the following data (total = 300), my output would be Apples = 40%, Oranges = 33.3% and Grapes = 26.7%. Will this be a calculated column or a measure? And how?

I took a jab at this and In my actual data, I have 7045 (81.4%) rows of Apples, 1615 (18.6%) rows of Oranges (which amounts to a total of 8660 rows. I tried the following formula:

AS % = COUNT(Sheet1[Name]) / Sheet1[Total Rows]

But I got a value of 100% for both when I filtered the values using the table visualization.

1 ACCEPTED SOLUTION
Microsoft

Maybe this?

`Percent of Total = DIVIDE( CALCULATE(COUNTROWS('Sheet1'),FILTER(ALL('Sheet1'), 'Sheet1'[Category] = EARLIER('Sheet1'[Category]))) , CALCULATE(COUNTROWS('Sheet1'),ALL('Sheet1'))) `

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

5 REPLIES 5
Microsoft

Here is a DAX solution.

Add a column to your table with the following formula

```Percent of Total = DIVIDE(
'Sheet1'[Inventory] ,
CALCULATE(
SUM(Sheet1[Inventory]),
ALL('Sheet1')))```

There are Quick Calc methods too that you can take advantage of.

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helper V

Hi Phil, appreciate the solution you made. However, what if I were using the # of rows of the category instead? Ie: out of 100 rows, I have 60 rows under Fruits, 40 under Vegetables, and I wanted to get the % of the categories? I tried this calculated column and got 100% for all categories:

DIVIDE(
COUNT(Sheet1[Category]),
COUNT(Sheet1[OrderID])
)

Edit: I got the values I needed using Quick Calc, however, I would still like to know if this is possible to do using DAX.

Microsoft

Maybe this?

`Percent of Total = DIVIDE( CALCULATE(COUNTROWS('Sheet1'),FILTER(ALL('Sheet1'), 'Sheet1'[Category] = EARLIER('Sheet1'[Category]))) , CALCULATE(COUNTROWS('Sheet1'),ALL('Sheet1'))) `

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helper V

This one worked for me:

```MEASURE = DIVIDE(
COUNTROWS('Sheet1'),
CALCULATE(
COUNTROWS('Sheet1'),
ALL('Sheet1')
)
)```

Your calculated column solution worked as well! Thank you!

Microsoft

Remember to format the column as a percent

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

## Helpful resources

Announcements

#### Launching new user group features

Learn how to create your own user groups today!

#### Check it Out!

Click here to read more about the November 2021 Updates!

#### Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors