March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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,
Solved! Go to 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.
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)
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,
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
62 | |
30 | |
25 | |
22 | |
18 |