Forum Discussion

Morgana_2022's avatar
Morgana_2022
Helper I
4 years ago
Solved

Counting values

How do I count some values within a field?

Ex. I have the "Fruit" field, made up of the values:
Banana
Banana
Strawberry
Kiwi

I have to get:
Banana = 2
Strawberry = 1
Kiwi = 1

My formula, butit does not work:

Fruit type = Sum ('fruit'[total]) - Sum ('fruit'[type] = "Banana")
DAX
DAX Calculation
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Morgana_2022 

    You can through ALLEXCEPT() to count values in groups .

     

    Measure = calculate(COUNT('Table'[Fruit]),ALLEXCEPT('Table','Table'[Fruit]))

     

    The result is as shown :

    Best Regards,
    Community Support Team _ Ailsa Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

4 Replies

  • serpiva64's avatar
    serpiva64
    Solution Sage
    4 years ago

    Hi,

    you can use a mesaure:

    Measure = count(Fruit[Fruit])
    or without a mesaure you can use aggregation:

     

    If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution !

     

     
    • Morgana_2022's avatar
      Morgana_2022
      Helper I
      4 years ago

      Hi,
      thanks! But how can I make a measure with the strawberry count alone?

      ex.
      Count ('Fruit' [Type] = "Strawberry")

      • serpiva64's avatar
        serpiva64
        Solution Sage
        4 years ago

        Hi,

        This is the measure 

        Measure2 = Calculate(count(Fruit[Fruit]),Fruit[Fruit]="Strawberry")
        or
        Measure3 = if(isfiltered(Fruit[Fruit]),sumX(Fruit, if(Fruit[Fruit]="Strawberry", count(Fruit[Fruit]),0)))

        but it depends what you need to do with it.

        If you use them in a table you obtain

         

         

         

        If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution !

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Morgana_2022 

    You can through ALLEXCEPT() to count values in groups .

     

    Measure = calculate(COUNT('Table'[Fruit]),ALLEXCEPT('Table','Table'[Fruit]))

     

    The result is as shown :

    Best Regards,
    Community Support Team _ Ailsa Tao
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.