cancel
Showing results for
Did you mean: Microsoft

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

1 ACCEPTED SOLUTION Microsoft

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: Best Regards,

Eyelyn Qin

11 REPLIES 11 Microsoft

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

Hi @v-chihyu ,

According to my understanding, you want to calculate total count / per categorys 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.

Best Regards,

Eyelyn Qin Microsoft

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 Microsoft

Best regards,

Eyelyn Qin Microsoft

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 Microsoft

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: Best Regards,

Eyelyn Qin Super User II

basically what you are looking for is Use the number column in teh values section and Average it.

Please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos, Press the thumbs up button!!👍
Regards,
Pranit Super User IV

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated! Microsoft

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. Super User III

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) Microsoft

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

Thanks,

Mark   