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

 

 

2 REPLIES 2
Super User
Super User

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



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

Proud to be a Datanaut!




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.

 

 

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 107 members 1,496 guests
Please welcome our newest community members: