cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
themistoklis New Contributor
New Contributor

Wrong Total in a Column and also on Dax Formulas

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.

image.png

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? 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
themistoklis New Contributor
New Contributor

Re: Wrong Total in a Column and also on Dax Formulas

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

 

View solution in original post

2 REPLIES 2
v-huizhn-msft Super Contributor
Super Contributor

Re: Wrong Total in a Column and also on Dax Formulas

Hi @themistoklis,

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.

 

1.GIF2.GIF

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

Subtotals and Grand Totals That Add Up “Correctly”


Best Regards,
Angelia

Highlighted
themistoklis New Contributor
New Contributor

Re: Wrong Total in a Column and also on Dax Formulas

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

 

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 337 members 3,242 guests
Please welcome our newest community members: