Forum Discussion

Palace27's avatar
Palace27
Regular Visitor
3 years ago
Solved

Create a Summary table based on other table

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 

Need Help
  • joaoribeiro's avatar
    joaoribeiro
    3 years ago

    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

  • DOLEARY85's avatar
    DOLEARY85
    3 years ago

    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]
     

     

3 Replies

  • joaoribeiro's avatar
    joaoribeiro
    Impactful Individual
    3 years ago

    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

  • DOLEARY85's avatar
    DOLEARY85
    Resident Rockstar
    3 years ago

    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]
     

     

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    3 years ago

    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 🙂.