cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JAMA
Frequent Visitor

Wrong total with SUM, FILTER, AND in Hub and Spoke

Hi,

 

I've played around with Power BI for some time now and this one is my first issue that I can't find a solution to in other posts.

 

So, I got my main hub table holding all my Sales-Data and for this case my Customer table that is a distinct representative of all customers in the Sales-Data table. The unique ID have a 1:M relation between the two tables. There are other tables with the same relation and I using the Date table for this column.

 

NetSales TwoMonth Back =
CALCULATE(SUM('Sales-Data'[Sales Price]),
FILTER('Sales-Data',
AND('Sales-Data'[CustomerID]= 'Customers'[CustomerID],
AND(OR('Sales-Data'[Vendor] = "Microsoft",'Sales-Data'[Vendor] = "Google"),
AND(YEAR('Sales-Data'[Actual Charge Start])=YEAR(Dates[2M-Back]),MONTH('Sales-Data'[Actual Charge Start])=MONTH(Dates[2M-Back]))
)
)
)
)
 
Data returned in the Column is not equal to the sales amount calculated, when operating within the Reseller-Data table.
 
Any suggestions?
Thank you all in advance. 
 
3 REPLIES 3
JAMA
Frequent Visitor

Thank you very much for the swift reply and confirming that the calculation should return the right result. In my effort to produce a dataset that would show the errors I have, I just got a sampleset confirming the math, now I'm back in finding why several customers have the same NS and others have 10 times what they should have, when the only difference is the amount of data...🤔

Hey @JAMA ,

 

I never said that the calculation should return the right result as I don't know your data.

I said break it down to smaller chunks tha you can process manually.

So start like this:

NetSales TwoMonth Back =
CALCULATE(
    SUM( 'Sales-Data'[Sales Price] ),
    FILTER(
        'Sales-Data',
        'Sales-Data'[CustomerID] = 'Customers'[CustomerID]
    )
)

 

Then check if this returns what you expected to return. Then add the next citeria:

NetSales TwoMonth Back =
CALCULATE(
    SUM( 'Sales-Data'[Sales Price] ),
    FILTER(
        'Sales-Data',
        'Sales-Data'[CustomerID] = 'Customers'[CustomerID]
            && ( 'Sales-Data'[Vendor] = "Microsoft"  )
    )
)

 

And then check again.

To be honest the following line looks a little suspicious to me:

'Sales-Data'[CustomerID]= 'Customers'[CustomerID]

 

Do you really have both CustomerIDs in your filter context?

Maybe you can post a screenshot of the table where the result should appear, this would make it easier to help you.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
selimovd
Super User
Super User

Hey @JAMA ,

 

try to break your problem down into smaller chunks. Start with one filter option, if this works add a second one and then check again.

Like this you will find out where the unwanted behavior happens.

 

By the way, how you do it is totally fine, but you're aware that you can also combine the filters with a "&&" instead of the nested AND or an "||" as an OR.

I personally find it more clear. So this code should give you the same result:

NetSales TwoMonth Back =
CALCULATE(
    SUM( 'Sales-Data'[Sales Price] ),
    FILTER(
        'Sales-Data',
        'Sales-Data'[CustomerID] = 'Customers'[CustomerID]
            && ( 'Sales-Data'[Vendor] = "Microsoft" || 'Sales-Data'[Vendor] = "Google" )
            && YEAR( 'Sales-Data'[Actual Charge Start] ) = YEAR( Dates[2M-Back] )
            && MONTH( 'Sales-Data'[Actual Charge Start] ) = MONTH( Dates[2M-Back] )
    )
)

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!