Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
zoubeyrB
Frequent Visitor

DAX and external filters

Hello,

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

model.png

 

 

 

 

 

 

 

 

 

 

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 :

case1.png

 

 

 

 

 

 

 

 

 

case2 :

case2.png

 

 

 

 

 

 

 

 

 

case 3:

cas3.png

 

 

 

 

 

 

 

 

 

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

Thanks in advance,



1 ACCEPTED SOLUTION
Selva-Salimi
Solution Specialist
Solution Specialist

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. 

View solution in original post

4 REPLIES 4
zoubeyrB
Frequent Visitor

Hello,

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

 

 

 

 

 

 

 

 

 

 

 

 

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)

case4.png
Selva-Salimi
Solution Specialist
Solution Specialist

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,

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors