Forum Discussion
Matrix drill-down > subtotal from multiple columns
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,
LydiaAnonymous thanks!
Works like a charm.