Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
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] )
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |