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...
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

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

Sorry for the confusion. Let me use another example.

 

filtration
clean
fresh
clean
fresh
clean
filtration
mineral
fresh
mineral
fresh
mineral
fresh
mineral
fresh
fresh
fresh
fresh
fresh
fresh

 

I have data like this, but I want to show the filtration % in my visualization. It should be mineral divided by totol counts. 

Thanks,

Mark

  • Anonymous's avatar
    Anonymous
    Not applicable
    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