Matrix drill-down > subtotal from multiple columns

[Anonymous]

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:
  • Task ID (link to other dataset)
  • Task Name
  • Task Budget
• Sub-tasks, containing - among others - the following columns:
  • Sub-task ID
  • Sub-task name
  • Sub-task budget
  • Task ID (link to other dataset)

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:

• the task budget always shown (when expanded it is shown as subtotal)
• the sub-task budget shown when it has a value in the dataset
• the sub-task budget not show any thing when it has no value in the dataset.

Can anybody help me achieve this?

[v-yuezhe-msft]

@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

[Anonymous]

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.

[v-yuezhe-msft · Accepted 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]

@v-yuezhe-msft thanks!

Works like a charm.