Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi there,
I have two tables
device_lookup(device name, model, country)
n1, a, US
n2, a, US
n3, b, US
n4, a, UK
deviceType_lookup(model, manufacturer)
a, lenovo
b, dell
c, acer
with slice to select the mode, how to caculate the percentage of selected devices by country.
ie. if user selected mode = a, i would like to get
US, 66.66%
UK, 100%
Thank you in advance!
Solved! Go to Solution.
Hi @elac ,
I create a file for your reference.
The measure I used:
percentage = var _count1=CALCULATE(COUNT('device_lookup'[model]),FILTER(ALLSELECTED(device_lookup),[country]=MAX('device_lookup'[country])))
var _count2=CALCULATE(COUNT('device_lookup'[country]),FILTER(ALL(device_lookup),[country]=MAX('device_lookup'[country])))
return DIVIDE(_count1,_count2)
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello amitchandak,
First, thank you so much for answer.
I tried both measures but it didn't give me the expected result.
device% = divide(countrows(device_lookup), calculate(countrows(device_lookup), removefilters(deviceType_lookup[model]) ) )
However, neither was giving the correct answer.
method 1 always return 100%
method 2 doesn't group on country, it acutally get the percentage on all devices in device lookup
Any thoughts? Any help is appreciated!
Elac
Hi @elac ,
I create a file for your reference.
The measure I used:
percentage = var _count1=CALCULATE(COUNT('device_lookup'[model]),FILTER(ALLSELECTED(device_lookup),[country]=MAX('device_lookup'[country])))
var _count2=CALCULATE(COUNT('device_lookup'[country]),FILTER(ALL(device_lookup),[country]=MAX('device_lookup'[country])))
return DIVIDE(_count1,_count2)
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Stephen,
This is terrific. It solves my problem perfectly.
If you don't mind, could you tell what is wrong with my original measure?
device%= divide(countrows(device_lookup), calculate(countrows(device_lookup), removefilters(deviceType_lookup[model]) ) )
Thank you so much!
Elac
@elac , Try a measure like
divide(countrows(Table), calculate(countrows(Table), removefilters(deviceType_lookup[model]) ) )
or
divide(countrows(Table), calculate(countrows(Table), all() ) )
User | Count |
---|---|
117 | |
75 | |
62 | |
50 | |
44 |
User | Count |
---|---|
174 | |
125 | |
60 | |
60 | |
57 |