Wrong Total in a Column and also on Dax Formulas

[themistoklis]

I have created a table on PowerBI and for one metric the total is incorrect.

More precisely the grand total for column 'TurnOver' is displayed as 43486,06.

If you export the table as a csv file and check the total you will see that it is completely different.

It is 71980.86 which is the correct value.

This total is also wrong on DAX formulas where the Turnover column is calculated. More specfically the % cost metric is wrong as the following formula is not working properly.

% Cost = SUM('Master Table - All Services'[PONetValue]) / SUM('Invoices to Clients'[TurnOver])

I think that i need to use the SUMMARIZE function together with the SUMX function but i dont know how to do it.

To download the PowerBI workspace please use this dropbox link.

Also can someone explain why the SUM function is not working and i need to use another function?

Is it because the tables 'Master Table' and 'Invoices to Clients' are linked to project ID and one project can have multiple tasks (many to one) so i need to use summarize? Is the join was one to one would i have the same issue? 

[v-huizhn-msft]

Hi @themistoklis,

I export the table as a csv file, it shows as follows, which is same with visual in Power BI. And I create another table including [fProjectGID] and sum([turnOver]), the total is also 4348606.

You can read the articles below, you will understand what the total works.

Subtotals and Grand Totals That Add Up “Correctly”

Best Regards,
Angelia

[themistoklis · Accepted solution]

Hi @v-huizhn-msft

Indeed both values are shown the same in PowerBI.

If you export the second table in a CSV file though you will notice that the total sum of the column is 71980.86 and not 43486.06

After some investigation i found out that this formula works ok.

SUMX(VALUES(Calculated_Measures[TaskGID]);CALCULATE(SUM('Invoices to Clients'[TurnOver])))

Thanks