Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to Solution.
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% |
Hello @vinayguptak0520
Happy to help.
Could you please correctly mark my answer as a solution, since, it has the DAX formula and thereby others can able to properly deduce the logic.
Best Regards,
Udit
Appreciate your Kudo 👍
🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree
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% |
I was able to figure out the syntax by modifying Udit's code.
Sales Actuals =
CALCULATE(
[Actual],
FILTER(
--ALL(SubcatTable),
ALLEXCEPT(SubcatTable,SubcatTable[GROUP]),
SubcatTable[Subcategory] = "Sales"
),
VALUES(SubcatTable[MonthYear])
)
Thank you for the help and suggestion Udit.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
43 | |
21 | |
21 | |
14 |
User | Count |
---|---|
124 | |
42 | |
39 | |
28 | |
24 |