Power BI with Microsoft Project Online Aggregate Columm wrong Summarization

[otgerpeidro]

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:

Task	TaskCost	TaskFinishDate	Aggregate
Task 1	10	01/06/2020	10
Task 2	20	02/06/2020	50
Task 3	20	02/06/2020	50

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,

[amitchandak]

@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]

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,

[v-yuta-msft]

@otgerpeidro ,

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

Regards,

Jimmy Tao