Get the % of an Item Category

[olimilo]

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.

[Phil_Seamark]

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.

[olimilo]

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.

[Phil_Seamark · Accepted solution]

Maybe this?

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

[olimilo]

This one worked for me:

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

Your calculated column solution worked as well! Thank you!

[Phil_Seamark]

Remember to format the column as a percent