Forum Discussion

acretney's avatar
acretney
Frequent Visitor
9 years ago
Solved

Need help with forecasting solution (I think i need the equivalent of SUMIFS)

Hi all,   Im a relative newby to PowerBI. Im basically looking to create an invoicing forecast visualization based on a Project Online data-feed. I think i have the right idea on what i need to do ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi acretney,

     

    If your datasource is like the screenshot, you can add a calculated column to get summary value based on same project and milestone:

    Proj/Milestone= 
CALCULATE(SUM(Table[VALUE]),Table[Project]=Earler(Table[Project]),Table[Milestone]=Earler(Table[Milestone]),ALLSELECT(Table))

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi acretney,


You can try to use below formulas if it suitable for your requirement:

 

Origianl table:

 

 

Table formula:

 

Table = SUMMARIZE('sample','sample'[Name],"Title","Barry the project"&[Name],"Total",SUM('sample'[Amount]))

Result:

 

In addition, you can also use measure to get the total amount.

 

Measure:

 

TotalAmount = SUMX(FILTER(ALL('sample'),[Name]=MAX([Name])),[Amount])

 

 

Regards,

Xiaoxin Sheng

 

 

acretney's avatar
acretney
Frequent Visitor
9 years ago

 

Thanks Xiaoxin,

 

I didnt know you could create table formulas. Unfortunately its not the solution in this case because there are multiple projects in the portfolio so hardcoding the project name in the formula wont work.

 

Perhaps a couple screenshots will make things clearer

 

Project 1 example

 

 

 

 

 

 

 

 

Project 2 example

 

 

 

 

 

 

 

 

 

This is indicitive of the situation across perhaps dozens of projects. You'll note i have already manually named tasks to represent the invoice milestones and they are each dated according to the tasks they are dependant on. Though it would be nice i dont need to generate the project + milestone via a formula because im adding that as a task name within each plan

 

So if i created a visualization looking for tasks that have [Duration = zero] i would get the following..

 

04/05 Scott the project M1

05/05 Barry the project M1

09/05 Scott the project M2

15/05 Barry the project M2

15/05 Scott the project M3

 

Where i'm stuck is a formula that goes with the above that will provide me the sum product value to go with each milestone. So the logic of the formula would be something like "give me the sum 'product value' of all tasks that have the same 'invoice milestones' as this task" which would result in the following based on the above examples...

 

04/05 Scott the project M1 [1550]

05/05 Barry the project M1 [1500]

09/05 Scott the project M2 [950]

15/05 Barry the project M2 [1050]

15/05 Scott the project M3 [350]

 

Thanks again for your assistance

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi acretney,

     

    You can refer to below steps to create a summary table:

     

    Original tables:

     

    1. Union tables and add tag.

     

    Table = SELECTCOLUMNS(
    UNION(
        ADDCOLUMNS(Barry,"Desc","Barry the project "&[Invoice Milestone]),
        ADDCOLUMNS(Scott,"Desc","Scott the project "&[Invoice Milestone])
        ),
        "Task",VALUE(RIGHT([Taks],2)),"Duration",VALUE(LEFT([Duration],2)),"Product Value",[Product Value],"Desc",[Desc])

     

     

    2. Summary table and calculate the data.

     

    Merged Table = SUMMARIZE('Table','Table'[Desc],"Total Duration",SUM('Table'[Duration]),"Task List",CONCATENATEX(VALUES('Table'[Task]),[Task],","),"Total Value",SUM('Table'[Product Value]))

     

     

    Notice: I upload the sample file below.

     

    BTW, if there are too many tables need to merge, you can try to add the tag at query editor side.

     

    Regards,

    Xiaoxin Sheng

    • acretney's avatar
      acretney
      Frequent Visitor
      9 years ago

      Thanks Xiaoxin,

       

      Unfortunately this wont work. The issue is that i will not know in advance what the constituent projects will be. I am working with the project online data-set. This updates itself organically as an when projects are added and updated within the portfolio system so the formulas need to be able to cope with this shifting landscape. Project Jeffrey and Project Dorothy could join the party at a moments notice and i wouldnt necessarily know about it and wouldnt be able to prepare for it.

       

      I actually have all the information i need within the 'Tasks' data-set that i get directly from project online so there shouldnt be any need for a table formula.

       

      I think what i need is a calculate column i can add to that data-set that behaves as follows...

       

       

      So in formula logic speak the formula would say...

       

      SUM the 'Value' of all rows where the 'Project' value matches this line AND the 'Milestone' value matches this line.

       

      This would completely solve my problem if possible

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi acretney,

         

        If your datasource is like the screenshot, you can add a calculated column to get summary value based on same project and milestone:

        Proj/Milestone= 
CALCULATE(SUM(Table[VALUE]),Table[Project]=Earler(Table[Project]),Table[Milestone]=Earler(Table[Milestone]),ALLSELECT(Table))

         

        Regards,

        Xiaoxin Sheng