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

 

 

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 229 members 2,501 guests
Please welcome our newest community members: