Forum Discussion
DAX and external filters
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,
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.
4 Replies
- Selva-Salimi
Solution Sage
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.
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,
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%).
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)
