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.
Hey,
I have a problem showing the correct subtotals in a matrix when drilling down to the next level.
What I have
2 datasets:
Now, what I want to do is show a matrix listing all the Task names as rows, and their respective data (e.g. budget).
As second row level in the matrix I use Sub-tasks. In each row I want to show several metrics, mostly based on the budget, like budget left, required per day, etc.
In my current setup however, I use the sum of Task budget as my leading budget amount. So, firstly the Task budget is carried over when I expand to the next level, like in the figure below:
Now the sub-task budget sometimes has a value, and sometimes doesn't. So the budget has to be calculated conditionally I guess.
Ideally, I want to have:
Can anybody help me achieve this?
Solved! Go to Solution.
@Anonymous,
Create the following measures in the projects table.
sum of task budget = SUM(tasks[Task Budget])
sum of project budget = CALCULATE(SUM(projects[Project Budget]),FILTER(projects,CALCULATE(COUNTROWS(FILTER(tasks,tasks[Project ID]=RELATED(projects[Project ID])))>0)))
Budget = IF(ISBLANK([sum of task budget]),[sum of project budget],[sum of task budget])
Regards,
Lydia
@Anonymous,
Could you please help to post dummy data of the above two tables and post expected result based on the dummy data here?
Regards,
Lydia
Hi Lydia,
Please find the pbix file containing the dummy data here: https://we.tl/aE9nawgnR8. I used a wetransfer link because I couldn't find a way to add a .pbix as attachment.
Now what I get is:
And what I want is this:
So, I want to still show all the project budgets, and want to show only the task budgets if they have a value. Also, it is important to have it in 1 column since I will be needing a lot of other metrics that are based on the budget (in a certain row).
I hope this clarifies it a bit more.
@Anonymous,
Create the following measures in the projects table.
sum of task budget = SUM(tasks[Task Budget])
sum of project budget = CALCULATE(SUM(projects[Project Budget]),FILTER(projects,CALCULATE(COUNTROWS(FILTER(tasks,tasks[Project ID]=RELATED(projects[Project ID])))>0)))
Budget = IF(ISBLANK([sum of task budget]),[sum of project budget],[sum of task budget])
Regards,
Lydia
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |