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
New forum boards available in Real-Time Intelligence.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Power BI Monthly Update - May 2024
Check out the May 2024 Power BI update to learn about new features.
Europe’s largest Microsoft Fabric Community Conference
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
