cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
themistoklis Memorable Member
Memorable Member

Wrong Column Totals and also wrong Total on DAX formula.

I have 2 tables in powerBI. One with tasks, projects and PONetValue and the second table has the projects and the TurnOver only.

On the first table a project can have multiple tasks so the connection between the two tables is many to one.

 

In the attached image i have the Vendor Code as Dimension and the PONetValue and TurnOver as metrics.

The PONet value is summed correctly (comes from the first table with the tasks and projects) but the TurnOver is not summed properly. It shows 43,486.06 instead of 70,680.06.

image.png

What i noticed, after extracting on csv all tasks with their projects and the Turnover value, is that the program first takes the distinct projects and then sums the values for the distinct projects and not all projects. Turnover is not a calculated field. It is just a simple field and its 'Default Summarization' is SUM. When i simply add it to the table it doesnt show the correct total. Same when i use it on DAX formulas to calculate percentages etc.

 

I think i have to use the summarize function or a different function as it seems that the logic behind the summation is that the program takes the distinct projects first and then sums the TurnOver values for the distinct projects. In my case i want ALL projects.

 

Dax Formula:

SUM('Invoices to Clients'[TurnOver])

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
themistoklis Memorable Member
Memorable Member

Re: Wrong Column Totals and also wrong Total on DAX formula.

@MFelix

Thanks for the reply.

 

After a lot of investigation i found out that the following formula works great:

 

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

 

Let me visualise the issue. On the following image i have the 2 tables.

 

image.png

In the old formula the summation was giving a wrong value. As you noticed for Turnover it gets the distinct projects, sums their values and gives the total number of 56,000 (which is wrong).

The new DAX formula takes the values of all projects across all tasks and sums their values and the correct total value is 128,213.

 

 

View solution in original post

2 REPLIES 2
Super User III
Super User III

Re: Wrong Column Totals and also wrong Total on DAX formula.

Hi @themistoklis,

 

How are you having the connection between vendor and the projects turn over?

 

Believe that the issues is refering that all the projects that a VendorCode as PONetValue are not returning the correct value since you don't have the connection between both.

 

I assume that in the PONetValue you have some kind of Vendor number try something like this:

 

 

calculate =
IF (
    HASONEFILTER ( Vendors[Vendor] );
    CALCULATE (
        SUM ( Turnover[turnover] );
        FILTER ( NetValue; NetValue[vendro] = MAX ( Vendors[Vendor] ) )
    );
    SUMX (
        Turnover;
        CALCULATE (
            SUM ( Turnover[turnover] );
            FILTER ( NetValue; NetValue[vendro] = MAX ( Vendors[Vendor] ) )
        )
    )
)

 

In my Case Turnover is the second table you refer and NetValue is the first one.

 

If you can share some sample data would I could help you better.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Check out my blog: Power BI em Português


Highlighted
themistoklis Memorable Member
Memorable Member

Re: Wrong Column Totals and also wrong Total on DAX formula.

@MFelix

Thanks for the reply.

 

After a lot of investigation i found out that the following formula works great:

 

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

 

Let me visualise the issue. On the following image i have the 2 tables.

 

image.png

In the old formula the summation was giving a wrong value. As you noticed for Turnover it gets the distinct projects, sums their values and gives the total number of 56,000 (which is wrong).

The new DAX formula takes the values of all projects across all tasks and sums their values and the correct total value is 128,213.

 

 

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors