Forum Discussion

otgerpeidro's avatar
otgerpeidro
New Member
6 years ago

Power BI with Microsoft Project Online Aggregate Columm wrong Summarization

 

Hi all,

 

I'm integrating Power BI and Microsoft Project Online and I have created a column which aggregates the sum of cost of each Project's task using the following calculation:

 

Aggregate = CALCULATE(SUM(TasksInformation[TaskCost]), FILTER(TasksInformation,TasksInformation[TaskFinishDate]<= EARLIER(TasksInformation[TaskFinishDate]) && TasksInformation[ProjectName] = EARLIER(TasksInformation[ProjectName])))
 
Then the result of this column is the following:
 
TaskTaskCostTaskFinishDateAggregate
Task 11001/06/202010
Task 22002/06/202050
Task 32002/06/202050

 

The Aggregate summarization is set to Sum in order to be represented in a Line Chart. The issue is that when is drawn in the chart for the day 02/06/2020 is drawing 100 instead of 50.

 

How could I avoid to sum twice the same amount for the same day? In order to have a linear graph without peaks made by the wrong sum amounts.

 

Feel free to revert for amy further clarification.

 

Thanks in advance for the help.

 

Regards,

 

Dashboards
Data Modeling

3 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    otgerpeidro ,

     

    Change the aggregation type from sum to max or min in the value section of line chart.

     

    Regards,

    Jimmy Tao

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    otgerpeidro , You can try like

    sumx(summarize(Table, Table[Date], "_1", sum(Table[Aggregate])),[_1])

     

    But I think you need to have an additional grouping of project id or something similar to that

    • otgerpeidro's avatar
      otgerpeidro
      New Member
      6 years ago

      Hi amitchandak ,

      When you say to try this sumx(summarize(Table, Table[Date], "_1", sum(Table[Aggregate])),[_1])

      Where should I try it? sorry I don't get your answer.

      Thanks.

      Regards,