Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Maybe this?
Percent of Total = DIVIDE(
CALCULATE(COUNTROWS('Sheet1'),FILTER(ALL('Sheet1'), 'Sheet1'[Category] = EARLIER('Sheet1'[Category]))) ,
CALCULATE(COUNTROWS('Sheet1'),ALL('Sheet1')))
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.
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')))
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |