Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi,
I want to create a measure that counts the number of '1's in one field [Rank] and divides it by the total number of cells that have a numerical value populated in [Score], in other words ignore 'UN' or blank cells. I will put this in a table or matrix as a group by
Score | Rank |
32 | 2 |
33 | 3 |
45 | 1 |
21 | 5 |
24 | 3 |
UN | - |
34 | 1 |
UN | - |
37 | 2 |
Result in this examaple would give 2/7
Thanks, MQ
Solved! Go to Solution.
// Let your table be T.
[Measure] =
var NumberOfOnes =
COUNTROWS(
FILTER(
T,
// T[Rank] must be a numerical field
// where "-" in your table must be
// a real BLANK. Do not mix numbers
// with text, please. Thanks.
T[Rank] = 1
)
)
var NumberOfNonblanks =
COUNTROWS(
FILTER(
T,
T[Score] <> "UN"
)
)
var Output =
DIVIDE( NumberOfOnes, NumberOfNonblanks )
return
Output
// Let your table be T.
[Measure] =
var NumberOfOnes =
COUNTROWS(
FILTER(
T,
// T[Rank] must be a numerical field
// where "-" in your table must be
// a real BLANK. Do not mix numbers
// with text, please. Thanks.
T[Rank] = 1
)
)
var NumberOfNonblanks =
COUNTROWS(
FILTER(
T,
T[Score] <> "UN"
)
)
var Output =
DIVIDE( NumberOfOnes, NumberOfNonblanks )
return
Output
User | Count |
---|---|
18 | |
18 | |
14 | |
13 | |
13 |
User | Count |
---|---|
17 | |
14 | |
14 | |
10 | |
8 |