DAX and external filters

[zoubeyrB]

Hello,

I have a small dataset (for training) using the following data model :

I would like to calculate the percentage of sales (quantity) for any country / city related to global sales for Germany. So I used the following expression : 

measure15 =
var currentSales = sum(sales[quantity])
var salesGermany = CALCULATE(SUM(sales[quantity]),ALLSELECTED(clients[city]),clients[country] = "Germany")
return
divide(currentSales,salesGermany)

The measure works properly as long as the field clients[city] isn't filtered :

case1 :

case2 :

case 3:

I find th 3rd result a bit puzzling, could you kindly provide an explanation (why the part salesGermany is blank) ?

Thanks in advance,

[zoubeyrB]

Hello,

I tried replacing ALLSELECTED with ALL, which led to the following result (measure 14) :

The issue of countries other than Germany disappearing is fixed. However, measure14 calculates the ratio between sales for a given country/city combination and sales for Germany unfiltered on city. That's why if we remove Hamburg we get 69.4 % for Germany (instead of 100%).

[zoubeyrB]

The following expression seems to be working :

measure14 = 

var sales_ = sum(sales[quantity])
var salesGermany = CALCULATE(SUM(sales[quantity]),
                              FILTER(ALLSELECTED(clients[city],clients[country]),clients[country] = "Germany")
                              )
return
divide(sales_,salesGermany)

[Selva-Salimi · Accepted solution]

Hi @zoubeyrB 

I think that you should use all instead of allselected.

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly. 

[zoubeyrB]

Hello,

It does make sense to use ALL instead of ALLSELECT (since the reference is all sales for Germany). I was just trying to make it work for training purposes.

Thanks for your help,