Hello everyone,
I am new to DAX and trying to figure out how to get the percentage by month by dividing a specific Subcategory = Sales.
Here is an example of the fake data by Month.
| Subcategory | Actuals | MonthYear |
| Allowances | (10,000.00) | Jan-24 |
| Allowances | (30,000.00) | Feb-24 |
| Cost of Goods Sold | (85,000.00) | Jan-24 |
| Cost of Goods Sold | (60,000.00) | Feb-24 |
| Deductions | - | Jan-24 |
| Deductions | (2,000.00) | Feb-24 |
| Direct Sales Expense | (25,000.00) | Jan-24 |
| Direct Sales Expense | (10,000.00) | Feb-24 |
| Discounts | - | Jan-24 |
| Discounts | (65,000.00) | Feb-24 |
| Return | (500.00) | Jan-24 |
| Return | (10,000.00) | Feb-24 |
| Sales | 500,000.00 | Jan-24 |
| Sales | 250,000.00 | Feb-24 |
This is what I am trying to achieve.
| MonthYear | Jan-24 | Feb-24 | Grand Total | |||
| Subcategory | Actuals | % | Actuals | % | Actuals | % |
| Sales | 500,000.00 | 100% | 250,000.00 | 100% | 750,000.00 | 100% |
| Allowances | (10,000.00) | -2.00% | (30,000.00) | -12.00% | (40,000.00) | -5.33% |
| Deductions | - | 0.00% | (2,000.00) | -0.80% | (2,000.00) | -0.27% |
| Discounts | - | 0.00% | (65,000.00) | -26.00% | (65,000.00) | -8.67% |
| Cost of Goods Sold | (85,000.00) | -17.00% | (60,000.00) | -24.00% | (145,000.00) | -19.33% |
| Return | (500.00) | -0.10% | (10,000.00) | -4.00% | (10,500.00) | -1.40% |
| Direct Sales Expense | (25,000.00) | -5.00% | (10,000.00) | -4.00% | (35,000.00) | -4.67% |
| Grand Total | 379,500.00 | 75.90% | 73,000.00 | 29.20% | 452,500.00 | 60.33% |
Each subcategory number should be divided by the total Sales number (500,000) for Jan 2024 and so forth for each month.
I appreciate all the help and suggestions. Thank you.
You can create the following measures to solve the problem:
1. A simple measure to get the "Actuals"
Actual = SUM(SubcatTable[Actuals])
Make sure to provide the custom format to this measure as: #,##0 ;(#,##0);-
2. To calculate the "Sales Actual", there are 2 situations:
a. You need a proper order of Sub-category,i.e., 1st row as "Sales", 2nd as "Allowances" etc. (as you have shown in screenshot)
b. You don't care about the order of sub-category, i.e., the items will be sorted alphabetically
In case you choose, the "a", the DAX to calculate the "Sales Actuals" is as follows:
Sales Actuals =
CALCULATE(
[Actual],
FILTER(
ALL(SubcatTable),
SubcatTable[Subcategory] = "Sales"
),
VALUES(SubcatTable[MonthYear])
)
To have the desired sorting, you need to have a "SortOrder" column in your dataset as shown in the following screenshot and you need to sort the "Sub-category" column by that "SortOrder" Column. In the screenshot, the "SortIndex" column is used for sorting the "Sub-category" column:
In case you choose, "b", the DAX to calculate the "Sales Actuals" is as follows:
Sales Actuals =
CALCULATE(
[Actual],
SubcatTable[Subcategory] = "Sales"
)
Then, you can create the "%" DAX measure with the following formula (This covers both "a" and "b" scenarios, so you don't have to worry):
% = DIVIDE([Actual], [Sales Actuals])
Here is a screenshot of the solution table in Power BI:
Best Regards,
Udit
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree
Thank you so much for the suggestion, Udit. I do have one more question. How do I calculate when I have another hierarchy level such as by Category (Group)?
Please see below the example data and visuals that I am trying to achieve. Thank you.
| Group | Subcategory | Actuals | MonthYear |
| Team A | Allowances | (10,000) | 24-Jan |
| Team A | Allowances | (30,000) | 24-Feb |
| Team A | Cost of Goods Sold | (85,000) | 24-Jan |
| Team A | Cost of Goods Sold | (60,000) | 24-Feb |
| Team A | Deductions | (10) | 24-Jan |
| Team A | Deductions | (2,000) | 24-Feb |
| Team A | Direct Sales Expense | (25,000) | 24-Jan |
| Team A | Direct Sales Expense | (10,000) | 24-Feb |
| Team A | Discounts | (10) | 24-Jan |
| Team A | Discounts | (65,000) | 24-Feb |
| Team A | Return | (500) | 24-Jan |
| Team A | Return | (10,000) | 24-Feb |
| Team A | Sales | 500,000 | 24-Jan |
| Team A | Sales | 250,000 | 24-Feb |
| Team B | Allowances | (15,000) | 24-Jan |
| Team B | Allowances | (11,000) | 24-Feb |
| Team B | Cost of Goods Sold | (32,000) | 24-Jan |
| Team B | Cost of Goods Sold | (12,000) | 24-Feb |
| Team B | Deductions | (150) | 24-Jan |
| Team B | Deductions | (5,000) | 24-Feb |
| Team B | Direct Sales Expense | (30,000) | 24-Jan |
| Team B | Direct Sales Expense | (12,000) | 24-Feb |
| Team B | Discounts | (100) | 24-Jan |
| Team B | Discounts | (8,000) | 24-Feb |
| Team B | Return | (5,000) | 24-Jan |
| Team B | Return | (40,000) | 24-Feb |
| Team B | Sales | 100,000 | 24-Jan |
| Team B | Sales | 145,000 | 24-Feb |
| MonthYear | 1/1/2024 | 2/1/2024 | Grand Total | ||||
| Group | Subcategory | Actuals | % | Actuals | % | Actuals | % |
| Team A | Sales | 500,000 | 100.00% | 250,000 | 100.00% | 750,000 | 100.00% |
| Allowances | (10,000) | -2.00% | (30,000) | -12.00% | (40,000) | -5.33% | |
| Deductions | (10) | 0.00% | (2,000) | -0.80% | (2,010) | -0.27% | |
| Discounts | (10) | 0.00% | (65,000) | -26.00% | (65,010) | -8.67% | |
| Cost of Goods Sold | (85,000) | -17.00% | (60,000) | -24.00% | (145,000) | -19.33% | |
| Return | (500) | -0.10% | (10,000) | -4.00% | (10,500) | -1.40% | |
| Direct Sales Expense | (25,000) | -5.00% | (10,000) | -4.00% | (35,000) | -4.67% | |
| SubTotal | 379,480 | 75.90% | 73,000 | 29.20% | 452,480 | 60.33% | |
| Team B | Sales | 100,000 | 100.00% | 145,000 | 100.00% | 245,000 | 100.00% |
| Allowances | (15,000) | -15.00% | (11,000) | -7.59% | (26,000) | -10.61% | |
| Deductions | (150) | -0.15% | (5,000) | -3.45% | (5,150) | -2.10% | |
| Discounts | (100) | -0.10% | (8,000) | -5.52% | (8,100) | -3.31% | |
| Cost of Goods Sold | (32,000) | -32.00% | (12,000) | -8.28% | (44,000) | -17.96% | |
| Return | (5,000) | -5.00% | (40,000) | -27.59% | (45,000) | -18.37% | |
| Direct Sales Expense | (30,000) | -30.00% | (12,000) | -8.28% | (42,000) | -17.14% | |
| SubTotal | 17,750 | 17.75% | 57,000 | 39.31% | 74,750 | 30.51% | |
| Grand Total | 397,230 | 66.21% | 130,000 | 32.91% | 527,230 | 52.99% |
You can create the following measures to solve the problem:
1. A simple measure to get the "Actuals"
Actual = SUM(SubcatTable[Actuals])
Make sure to provide the custom format to this measure as: #,##0 ;(#,##0);-
2. To calculate the "Sales Actual", there are 2 situations:
a. You need a proper order of Sub-category,i.e., 1st row as "Sales", 2nd as "Allowances" etc. (as you have shown in screenshot)
b. You don't care about the order of sub-category, i.e., the items will be sorted alphabetically
In case you choose, the "a", the DAX to calculate the "Sales Actuals" is as follows:
Sales Actuals =
CALCULATE(
[Actual],
FILTER(
ALL(SubcatTable),
SubcatTable[Subcategory] = "Sales"
),
VALUES(SubcatTable[MonthYear])
)
To have the desired sorting, you need to have a "SortOrder" column in your dataset as shown in the following screenshot and you need to sort the "Sub-category" column by that "SortOrder" Column. In the screenshot, the "SortIndex" column is used for sorting the "Sub-category" column:
In case you choose, "b", the DAX to calculate the "Sales Actuals" is as follows:
Sales Actuals =
CALCULATE(
[Actual],
SubcatTable[Subcategory] = "Sales"
)
Then, you can create the "%" DAX measure with the following formula (This covers both "a" and "b" scenarios, so you don't have to worry):
% = DIVIDE([Actual], [Sales Actuals])
Here is a screenshot of the solution table in Power BI:
Best Regards,
Udit
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree
Thank you so much for the suggestion, Udit. I do have one more question. How do I calculate when I have another hierarchy level such as by Category (Group)?
Please see below the example data and visuals that I am trying to achieve. Thank you.
| Group | Subcategory | Actuals | MonthYear |
| Team A | Allowances | (10,000) | 24-Jan |
| Team A | Allowances | (30,000) | 24-Feb |
| Team A | Cost of Goods Sold | (85,000) | 24-Jan |
| Team A | Cost of Goods Sold | (60,000) | 24-Feb |
| Team A | Deductions | (10) | 24-Jan |
| Team A | Deductions | (2,000) | 24-Feb |
| Team A | Direct Sales Expense | (25,000) | 24-Jan |
| Team A | Direct Sales Expense | (10,000) | 24-Feb |
| Team A | Discounts | (10) | 24-Jan |
| Team A | Discounts | (65,000) | 24-Feb |
| Team A | Return | (500) | 24-Jan |
| Team A | Return | (10,000) | 24-Feb |
| Team A | Sales | 500,000 | 24-Jan |
| Team A | Sales | 250,000 | 24-Feb |
| Team B | Allowances | (15,000) | 24-Jan |
| Team B | Allowances | (11,000) | 24-Feb |
| Team B | Cost of Goods Sold | (32,000) | 24-Jan |
| Team B | Cost of Goods Sold | (12,000) | 24-Feb |
| Team B | Deductions | (150) | 24-Jan |
| Team B | Deductions | (5,000) | 24-Feb |
| Team B | Direct Sales Expense | (30,000) | 24-Jan |
| Team B | Direct Sales Expense | (12,000) | 24-Feb |
| Team B | Discounts | (100) | 24-Jan |
| Team B | Discounts | (8,000) | 24-Feb |
| Team B | Return | (5,000) | 24-Jan |
| Team B | Return | (40,000) | 24-Feb |
| Team B | Sales | 100,000 | 24-Jan |
| Team B | Sales | 145,000 | 24-Feb |
| MonthYear | 1/1/2024 | 2/1/2024 | Grand Total | ||||
| Group | Subcategory | Actuals | % | Actuals | % | Actuals | % |
| Team A | Sales | 500,000 | 100.00% | 250,000 | 100.00% | 750,000 | 100.00% |
| Allowances | (10,000) | -2.00% | (30,000) | -12.00% | (40,000) | -5.33% | |
| Deductions | (10) | 0.00% | (2,000) | -0.80% | (2,010) | -0.27% | |
| Discounts | (10) | 0.00% | (65,000) | -26.00% | (65,010) | -8.67% | |
| Cost of Goods Sold | (85,000) | -17.00% | (60,000) | -24.00% | (145,000) | -19.33% | |
| Return | (500) | -0.10% | (10,000) | -4.00% | (10,500) | -1.40% | |
| Direct Sales Expense | (25,000) | -5.00% | (10,000) | -4.00% | (35,000) | -4.67% | |
| SubTotal | 379,480 | 75.90% | 73,000 | 29.20% | 452,480 | 60.33% | |
| Team B | Sales | 100,000 | 100.00% | 145,000 | 100.00% | 245,000 | 100.00% |
| Allowances | (15,000) | -15.00% | (11,000) | -7.59% | (26,000) | -10.61% | |
| Deductions | (150) | -0.15% | (5,000) | -3.45% | (5,150) | -2.10% | |
| Discounts | (100) | -0.10% | (8,000) | -5.52% | (8,100) | -3.31% | |
| Cost of Goods Sold | (32,000) | -32.00% | (12,000) | -8.28% | (44,000) | -17.96% | |
| Return | (5,000) | -5.00% | (40,000) | -27.59% | (45,000) | -18.37% | |
| Direct Sales Expense | (30,000) | -30.00% | (12,000) | -8.28% | (42,000) | -17.14% | |
| SubTotal | 17,750 | 17.75% | 57,000 | 39.31% | 74,750 | 30.51% | |
| Grand Total | 397,230 | 66.21% | 130,000 | 32.91% | 527,230 | 52.99% |
Helpful resources
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
