Create a Summary table based on other table

[Palace27]

Hello

I have a table to followup project's costs, with one line per Purchase order /invoice, having one column as well for the budget. I would like to have a summary table with one line per project summarizing all numbers, as here below:

I think it is self explanatory but just in case:

• "Amoun in PO" and "Amount in Invoice" would be a total sum per project
• "Total spent" is PO+Invoice totals
• "Budget" is the one that is repeated every row of the correspondin project
• "Remaining Budget" is Budget-Total Spent

Thanks!!

Jose 

[joaoribeiro · Accepted solution]

Hi @Palace27 ,

You can create a "Reference" to this table in Power Query in order to create the new one. After that, you can use the "Group By" option to group your data by "Project Id" with the desired aggregation function for each column. Please check screenshots below:

After creating the Reference, delete the columns that are not value and will not be used for the grouping (PO Number, for instance), and then select Group By:

Select a aggregation function for each column with values:

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍

Thanks!

Best regards,
Joao Ribeiro

[Syndicate_Admin]

Hello @Syndicate_Admin you can create a dimension called "Projects" and connect it to your summary table, then you can create metrics that solve your problem

I leave here the PBI file,

example.pbix

If my answer seems valid, consider marking it as a solution 🙂.

[DOLEARY85 · Accepted solution]

Hi,

it looks like you could use a series of measures:

Measure PO Amount = if(isblank(calculate(sum(project[PO Amount]))),0,calculate(sum(project[PO Amount])))

Measure Invoice Amount = if(isblank(calculate(sum(project[Invoice amount]))),0,calculate(sum(project[Invoice amount])))

Total Spent Measure = [Measure PO Amount]+[Measure Invoice Amount]

Measure budget = max(project[Total Budget])

Remaing Measure = [Measure budget]-[Total Spent Measure]

[joaoribeiro · Accepted solution]

Hi @Palace27 ,

You can create a "Reference" to this table in Power Query in order to create the new one. After that, you can use the "Group By" option to group your data by "Project Id" with the desired aggregation function for each column. Please check screenshots below:

After creating the Reference, delete the columns that are not value and will not be used for the grouping (PO Number, for instance), and then select Group By:

Select a aggregation function for each column with values:

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍

Thanks!

Best regards,
Joao Ribeiro