cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Wrong totals on SUMX column

 

 

 

In order to compare the numbers from two systems who have a different granularity, I need to make some adjustments to the table with the higher granularity. One of them is to avoid double counting.

 

I’ve done this to count the amount of ID’s (OID). If they are multiplied then the sales amount needs to be divided to arrive at the correct amount. Then I correct the sales in a measure to divide the sales by the count of the OID in the following measure: 

 

Sales Corrected := CALCULATE(
    SUMX(Recon_Detailed,Recon_Detailed[CRM-Turnover]/COUNT(Recon_Detailed[NAV-OID])))

This gives the desired result on a line level, but not on a grand total level. Because than the divide shouldn't happen, just a sum of the result of the Sales Corrected. How can I correct this? I've already searched through the forum, but couldn't find exactly what I needed. See below an example of the data. At the Total of Sales Corrected I would like to see €2.460.884,07 instead of €351.726,30

 

 

ID issue.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Wrong totals on SUMX column

Try converting your Sales Corrected to use measures instead and changing your SUMX to use the unique NAV-OID.

Sales = SUM ( Recon_Detailed[CRM-Turnover] )
CountOID = COUNT ( Recon_Detailed[NAV-OID] )
Sales Corrected = SUMX ( VALUES ( Recon_Detailed[NAV-OID] ), [Sales] / [CountOID] )

sumx.jpg 

View solution in original post

5 REPLIES 5
tex628 New Contributor
New Contributor

Re: Wrong totals on SUMX column

The divide will not work.
You should filter on distinct order ID's when you calculate the sum instead, this will not give you the issue on the total. 

Re: Wrong totals on SUMX column

Thanks for your response @tex628 . This won't work because the line is unique, due to information from the 2 systems. An example:

 

In system A ID 10 can have multiple lines with different dates which can be different invoices coming in at different times. In system B ID 10 doesn't have a date. So for every time I have a line in system A for ID 10 I will have to do a lookup to system B where I only have 1 record with the whole amount. 

 

If I would try to keep only unique ID's from system B I will lose lines from system A. Since I'm working on a YTD report where I can aggregate to a Month the issue only occurs when an ID has been bookes over different months.

tex628 New Contributor
New Contributor

Re: Wrong totals on SUMX column

Try this: 

 

Sales Corrected := CALCULATE(
DIVIDE(SUMX(Recon_Detailed,Recon_Detailed[CRM-Turnover] ,
COUNT(Recon_Detailed[NAV-OID]-DISTINCTCOUNT(NAV-OID)) ,
SUMX(Recon_Detailed,Recon_Detailed[CRM-Turnover])

 

 

Super User
Super User

Re: Wrong totals on SUMX column

Try converting your Sales Corrected to use measures instead and changing your SUMX to use the unique NAV-OID.

Sales = SUM ( Recon_Detailed[CRM-Turnover] )
CountOID = COUNT ( Recon_Detailed[NAV-OID] )
Sales Corrected = SUMX ( VALUES ( Recon_Detailed[NAV-OID] ), [Sales] / [CountOID] )

sumx.jpg 

View solution in original post

Re: Wrong totals on SUMX column

@jdbuchanan71Awesome! Did the trick Smiley Very Happy

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: 141 members 1,422 guests
Please welcome our newest community members: