cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
olimilo
Helper V
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:

 

 

NameCategoryOrder DateInventory
ApplesFruits1/1/17100
OrangesFruits1/2/1770
GrapesFruits1/3/1780
ApplesFruits1/4/1720
OrangesFruits1/5/1730

 

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

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!

View solution in original post

5 REPLIES 5
Phil_Seamark
Microsoft
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!

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.

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!

View solution in original post

This one worked for me:

 

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

 

Your calculated column solution worked as well! Thank you!

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

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.