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.
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 |
Solved! Go to Solution.
Does this do what you want?
Desired Output =
1 / CALCULATE ( COUNTROWS ( Table1 ), ALLEXCEPT ( Table1, Table1[Order #] ) )
Does this do what you want?
Desired Output =
1 / CALCULATE ( COUNTROWS ( Table1 ), ALLEXCEPT ( Table1, Table1[Order #] ) )
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.
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.
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
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.
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
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.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |