Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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:
Did I answer your question? Please mark my reply as solution ~
Best Regards,
Eyelyn Qin
Is this added as a new column, or a new measure?
This is great, the first one is exactly the one what I need.
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
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:
Did I answer your question? Please mark my reply as solution ~
Best Regards,
Eyelyn Qin
HI @v-chihyu
basically what you are looking for is
Use the number column in teh values section and Average it.
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 |
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |