All Sales Within Territory

[Andrew_UK]

Hi All,

I have a model built for Gap Analysis so that our sales team can compare their customers against others and look for opportunities (by category/product/brand/pack size etc...)

The comparisons are "vs Same Customer Prior Year", "vs Country", and "vs Global".

The "vs Global" measure is easy because it's simply all(Customer[Company Name]), the "Same Customer Prior Year" is easy enough and it just requires fiddling around with the calendar table, the "vs Country" is a tricky one though!! Every Customer has 1 country (but obviously 1 country can have many customers), so when selecting 1 customer you are by default selecting 1 territory.

I'm also able to identify which country that is, I tested the formula =IF(Hasonevalue(Customer[Country Name]),Values(Customer[Country Name]),Blank())

In the instance where I select "Company A" it returns "France" correctly - So I need to build a measure that returns ALL French sales when "Company A" is selected. I've tested and I can do this very easily by insisting that the sales person also select the territory in a separate slicer, but that's extra faff for the end user. Given that it's 1 country per company there must be a way of doing this.

Has anyone else come up against the same? What solutions did you find?

Thanks in advance

Andrew

 

[Andrew_UK]

I dug out some of my books on Power Pivot and built it up slowly from the first principles of everything. I had thought my answer might lie in nesting all within filter but Collie and Singh (Power Pivot and Power BI:The Excel Users Guide to Data Revolution, 2016) describes on page 238 the order of how filters are applied, so I was unable to then have it nested and linking back to the original Measure result. Urghhh

Then I found the answer... Calculate can use a mix of filters AND True/False expressions. So I tested dropping my filter term in as a straight out expression, with the original table function for All and it worked :D

Country Cases:=IF(Hasonevalue(Customer[Country Name]),Calculate(Sum(Analysis[Cases Sold]),All(Customer),Analysis[Country]=VALUES(Customer[Country])),Blank())