Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
v-chihyu
Employee
Employee

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
v-chihyu
Employee
Employee

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

v-eqin-msft
Community Support
Community Support

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

pranit828
Community Champion
Community Champion

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.

 





PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile
mahoneypat
Employee
Employee

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.

harshnathani
Community Champion
Community Champion

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.