Forum Discussion

v-chihyu's avatar
v-chihyu
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago
Solved

count category column and divide it

hi

I am very new to this. I need to count each item in the category and divide by total number.

Category
Watermelon
Banana
Apple


it's simple, but I just couldn't make it calculate. 

I tried many calculate split, and filter function. the syntax is wrong 

 

Thanks,

Mark

Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi v-chihyu ,

    It seems like you just want to calculate the count of Mineral / total count,right?

    You could try to use the following formula:

     

    JustForMineral =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER ( ALL ( 'Table' ), 'Table'[filtration] = "mineral" )
)
    / CALCULATE ( COUNTROWS ( ALL ( 'Table' ) ) )

     

    In case you want to calculate each value in Filtration column, use this:

     

    eachFiltration =
CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[filtration] ) )
    / CALCULATE ( COUNTROWS ( ALL ( 'Table' ) ) )

     

    My visual looks like this:

    Did I answer your question? Please mark my reply as solution ~

     

    Best Regards,

    Eyelyn Qin

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi v-chihyu ,

     

    Not very clear.

     

    Can you share the expected output.

     


    Harsh Nathani
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

     

    • v-chihyu's avatar
      v-chihyu
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      My expect outcome will be the percentage of total Apple counts divided by total counts in category. 

       

      Thanks, 

      Mark

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    Please try this measure expression, replacing Table with your actual table name.  Use it in a table visual with your Category column.

     

    Pct of Total Category Count = COUNTROWS(Table)/CALCULATE(COUNTROWS(Table), ALL(Table[Category]))

     

    Then format it as a percentage.

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

     

    • v-chihyu's avatar
      v-chihyu
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      I tried this formula, it's almost there. 

      I know how to how to get this with table, but it's nice to the formula. 

      Learn one thing, thanks.

       

      My expectation is to only get only either Apple or banana or watermelon.

      Thanks.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI v-chihyu 

     

    basically what you are looking for is 

    Use the number column in teh values section and Average it.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi v-chihyu ,

    According to my understanding, you want to calculate total count / per category’s count, right?

     

    You could use the following formula:

    percentageCategory =
CALCULATE (
    COUNTROWS ( perCategory ),
    ALLEXCEPT ( perCategory, perCategory[Category] )
)
    / CALCULATE ( COUNTROWS ( ALL ( perCategory ) ) )

    My visualization looks like this:

    Is the result what you want? If you have any questions, please upload some data samples and expected output.

    Please do mask sensitive data before uploading.

     

    Best Regards,

    Eyelyn Qin

     

    • v-chihyu's avatar
      v-chihyu
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      I tried this formula, but I don't know how to get perCategory in the formula.

       

      I think your formula is almost there. My expectation is to get either Apple or watermelon or banana.

       

      Thanks,

      Mark

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        v-chihyu  Sorry but I'm not very clear about  your requirement...

        Could you upload some insensitive data sample and Please describe your expected output in more detail?

         

        Best regards,

        Eyelyn Qin

  • v-chihyu's avatar
    v-chihyu
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    This is great, the first one is exactly the one what I need.