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

[JAMA]

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. 

[JAMA]

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

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic