Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MauriceMecowe
Resolver II
Resolver II

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
jdbuchanan71
Super User
Super User

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
jdbuchanan71
Super User
Super User

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 

@jdbuchanan71Awesome! Did the trick Smiley Very Happy

tex628
Community Champion
Community Champion

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. 


Connect on LinkedIn

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.

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

 

 


Connect on LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.