count until rank category

[jorgeslzr]

I have a table with elements that belong to a category. Both the element and the category are on the table viz as shown:

[Element]    [Category]

      a                  x

      b                  y

      c                  z

      d                  x

      e                 y

      f                  y

but the categories are ranked. I mean:

[Category]    [Level of importance]

      x                             1

      y                             2

      z                             3

What I want is to count on the first table, how many elements appear, for its category OR HIGHER. I want this:

[Element]   [Category]  [Count]

      a                  x              2    (2 x's)

      b                  y              5   (because there are 3 y's and 2 x's)

      c                  z               6   (3 y's, 2 x's, 1 z)

      d                  x              2

      e                 y               5

      f                  y               5

I hope I explained myself.

Thanks

[JustJan · Accepted solution]

Hi@jorgeslzr 

I think this should do the trick:

Count = 
var _CurrentCat = SELECTEDVALUE(ElemCat[Category])
var x = 
CALCULATE(
 countrows(ElemCat),
 all(ElemCat),
 ElemCat[Category] <= _CurrentCat
)

var result = x

return result

Jan

[harshnathani]

Hi @jorgeslzr ,

Thanks for explaining. But it gets confusing sometime seeing x, y's and z's.

Can you share some sample data and expected output ?

Regards,

HN