Forum Discussion
Calculate Sales by Delivery Country
Yep, so it's the same formula you are using currently with your measures, but put a "Calculate" function around them. Should look something like this:
PY_USA = Calculate(your existing previous year formula, filter('Countries', 'Countries'[name]="USA"))Same with the Previous year -1.
So, what happens is if there are no locations of a customer within the country, your total for this measure should result in 0
Keith-Sayer Yes, I do have a structure as you mentioned. However, when I want to display this in a line and clustered column chart (see screenshot below), it splits Net Sales correctly (blue column for Belgium and yellow for Germany) but not Net Sales PY (Net Sales last year) and Net Sales PY-1 (Net Sales 2 years ago). Is there any solution to this? This is why I would opt to write a new measure to split this.
Thanks!
Oh I see, you need that line to be split by color as well?
I don't think that's possible as a line (you would really need multiple lines to visualize this type of data in line chart). You could definitely put your previous year and previous year -1 measures as additional columns and then it should split them out appropriately.
The other option is to set Country as the "x-axis" and then put what you have currently in the x-axis as a legend, that should work. The caveat being, previous year and prior year measures won't split by this factor.