Wrong Column Totals and also wrong Total on DAX formula.

[themistoklis]

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.

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])

[MFelix]

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

[themistoklis · Accepted solution]

@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.

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.