cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Microsoft
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

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:

8.25 follow 1.PNG

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

 

Best Regards,

Eyelyn Qin

View solution in original post

11 REPLIES 11
Microsoft
Microsoft

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

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

8.21.2.1.png

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

 

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

@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

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

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:

8.25 follow 1.PNG

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

 

Best Regards,

Eyelyn Qin

View solution in original post

Super User II
Super User II

HI @v-chihyu 

 

basically what you are looking for is 

pranit828_0-1597977489906.png

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

 





Hope it resolves your issue? 
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
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


I tried this formula, it's almost there. 

v-chihyu_0-1598023855446.png

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

 

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

 

Thanks, 

Mark

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors