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.
I am trying to allocate shared expenses to sub departments. my data structure:
Expense category | Sub-department | Department | Month-year | Expenses |
cat1 | sub1 | dept1 | Jan-21 | 10 |
cat2 | sub1 | dept1 | Jan-21 | 10 |
cat1 | sub2 | dept1 | Jan-21 | 10 |
cat2 | sub2 | dept1 | Jan-21 | 10 |
cat1 | sub3 | dept1 | Jan-21 | 10 |
cat2 | sub3 | dept1 | Jan-21 | 10 |
cat1 | sub4 | dept2 | Jan-21 | 10 |
cat2 | sub4 | dept2 | Jan-21 | 10 |
cat1 | sub5 | dept2 | Jan-21 | 10 |
cat2 | sub5 | dept2 | Jan-21 | 10 |
cat1 | shared | dept1 | Jan-21 | 50 |
cat2 | shared | dept1 | Jan-21 | 50 |
cat1 | shared | shared | Jan-21 | 50 |
cat2 | shared | shared | Jan-21 | 50 |
I have departments and sub-departments structured in the format above. I have a seperate revenue and COGS calculation by month (Through date table). The above table is connected to date table by date (one date per month), sub department to another table for that sub-department connection.
I have calculated % of total revenue by department and % of total revenue of everything which looks something like this:
department | sub | % of department revenue | % of total revenue |
dept1 | sub1 | 20% | 20% |
dept1 | sub2 | 40% | 20% |
dept1 | sub3 | 40% | 20% |
dept2 | sub4 | 50% | 20% |
dept2 | sub5 | 50% | 20% |
% of department revenue formula:
% of total revenue formula:
department | sub | % of department revenue | % of total revenue | Department shared allocation | Total shared allocation |
dept1 | sub1 | 20% | 20% | $20 | $20 |
dept1 | sub2 | 40% | 20% | $40 | $20 |
dept1 | sub3 | 40% | 20% | $40 | $20 |
dept2 | sub4 | 50% | 20% | $0 | $20 |
dept2 | sub5 | 50% | 20% | $0 | $20 |
Total | 200% | 100% | $100.00 | $100 |
Solved! Go to Solution.
Figured out the solution,
Created a reference table from the original table of expenses and filtered on only shared expenses and linked that to only date table, no joins to expenses or department tables ( so it does not auto filter).
Model:
Created two seperate dax for calculating individual departments shared expenses and used the % of revenue formula stated in the original post:
Figured out the solution,
Created a reference table from the original table of expenses and filtered on only shared expenses and linked that to only date table, no joins to expenses or department tables ( so it does not auto filter).
Model:
Created two seperate dax for calculating individual departments shared expenses and used the % of revenue formula stated in the original post:
Could you provide the data in the other tables? The information you provide is not enough to create a calculation formula. If possible, it is recommended to provide the pbix file (delete sensitive information), so that the table data and table relationships can be seen more clearly, so as to better deal with the problem.
Best Regard
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
82 | |
64 | |
63 | |
56 |
User | Count |
---|---|
171 | |
113 | |
110 | |
73 | |
73 |