Filtering Countrows

[Anonymous]

I need to calculate how much of an order is in each category.  I need a column calculation that divides the entire order by however many rows are involved.  How would the Dax be formed for this? I only see countrows working for the whole table, and not for a column individually.  Is there a way to filter this? 

Order #	Category	Desired Output
1	a	0.25
1	a	0.25
1	b	0.25
1	b	0.25
2	a	0.5
2	a	0.5
3	a	0.25
3	a	0.25
3	a	0.25
3	b	0.25

[AlexisOlson · Accepted solution]

Does this do what you want?

Desired Output =
1 / CALCULATE ( COUNTROWS ( Table1 ), ALLEXCEPT ( Table1, Table1[Order #] ) )

[Anonymous]

YES.  That was way easier than how I solved it.  I completely forgot about ALLEXCEPT.  I'll remember this for future projects.  I often have to group by category. 

[Anonymous]

I used Power Query to resolve this issue.  I duplicated my table and grouped by order number to get a count of everything under that order number to get a count of lines.  I then did 1/that column's value and got what I was looking for.

[mohammedadnant]

Hi @Anonymous 

Good day,

If you need count of orders by category, Just add category and orders in table visual,

beside the orders column in the table visual under visualiztion pane --> click on the "v" icon --> click on count.

you will the count of orders by category

Hope this helps.

Thanks & Regards,

Mohammed Adnan

https://www.youtube.com/c/taik18

[Anonymous]

This is still wrong as I am comparing categories with this data and it will count an order over multiple categories.  In the case of order 1, it will add a full count to A and B, where I only want .5 added for both.

[mohammedadnant]

then you can use category, and instead of order column, add desired output column --> click "v" to select sum, by doing this you will get 

category | count

a             | .5

b             | .5

Regards

Mohammed Adnan

[Anonymous]

Maybe I'm not understanding, but how would summing a category that is text = .5?

Added: Also, to clarify, that .25 doesn't exist....that is what I'm trying to get to.