cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
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

 

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

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

 

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 6 members 1,279 guests
Please welcome our newest community members: