Forum Discussion
Matrix drill-down > subtotal from multiple columns
Hey, I have a problem showing the correct subtotals in a matrix when drilling down to the next level. What I have 2 datasets: Tasks, containing - among others - the following columns: Tas...
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
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
Anonymous thanks!
Works like a charm.