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

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 New Contributor
New Contributor

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 Felix


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

Proud to be a Datanaut!




Highlighted
themistoklis New Contributor
New Contributor

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
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors