Forum Discussion
Count Rows based on a value in a column
Hi,
I'm wondering if anyone can help.
I am trying to divide the $ amount in route revenue evenly by the number of rows an order header number has.
So in the first example below it would be $1,024.52 / 8 rows = $128.07
The second example would be $1,000 / 4 rows = $250
Currently, each row is pulling the total revenue ($1,024.52) for an order, but I need it split between each row.
I have been playing around with the count rows function, but keep getting errors.
| RouteNumber | Stop Number | ord_hdrnumber | LegHeaderEndDate | Week Number | Route Revenue | Split Revenue |
| MAA015P | 14375327 | 2136381 | 9/28/2020 16:26 | 40 | $1,024.52 | $ 128.07 |
| MAA015P | 14375332 | 2136381 | 9/28/2020 16:26 | 40 | $1,024.52 | $ 128.07 |
| MAA015P | 14375330 | 2136381 | 9/28/2020 16:26 | 40 | $1,024.52 | $ 128.07 |
| MAA015P | 14375328 | 2136381 | 9/28/2020 16:26 | 40 | $1,024.52 | $ 128.07 |
| MAA015P | 14375326 | 2136381 | 9/28/2020 16:26 | 40 | $1,024.52 | $ 128.07 |
| MAA015P | 14375331 | 2136381 | 9/28/2020 16:26 | 40 | $1,024.52 | $ 128.07 |
| MAA015P | 14375329 | 2136381 | 9/28/2020 16:26 | 40 | $1,024.52 | $ 128.07 |
| MAA015P | 14375325 | 2136381 | 9/28/2020 16:26 | 40 | $1,024.52 | $ 128.07 |
| $8,196.20 | $1,024.52 | |||||
| MAA024V | 1234 | 2184563 | 9/30/2020 19:30 | 48 | $1,000.00 | $ 250.00 |
| MAA024V | 1235 | 2184563 | 9/30/2020 19:30 | 48 | $1,000.00 | $ 250.00 |
| MAA024V | 1236 | 2184563 | 9/30/2020 19:30 | 48 | $1,000.00 | $ 250.00 |
| MAA024V | 1237 | 2184563 | 9/30/2020 19:30 | 48 | $1,000.00 | $ 250.00 |
| $4,000.00 | $1,000.00 |
Hi clarkey1988
Try this for a calc column:
Split Revenue V2 = DIVIDE ( Table1[Route Revenue], CALCULATE ( COUNT ( Table1[ord_hdrnumber] ), ALLEXCEPT ( Table1, Table1[ord_hdrnumber] ) ) )Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
3 Replies
Hi clarkey1988
Try this for a calc column:
Split Revenue V2 = DIVIDE ( Table1[Route Revenue], CALCULATE ( COUNT ( Table1[ord_hdrnumber] ), ALLEXCEPT ( Table1, Table1[ord_hdrnumber] ) ) )Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi AlB , I appreciate your help.
That worked. I received your message and will respond. I do have a quick follow up. I believ eit's similar to the above.
I have a specific route number called Nestle-12 that I invoice $14,100 each week. I again need to divide this $14,100 by the number of orders for Nestle-12 for that given week. So the example below would be $14,100/9 = $1,566.67 each row.
It's similar to the above but it's specific to a route number and week number.
Yes, if it's specific to route and week number, you just have to reflect that on the ALLEXCEPT... and let context transition work its magic:
Split Revenue V3 = DIVIDE ( Table1[Route Revenue], CALCULATE ( COUNT ( Table1[ord_hdrnumber] ), ALLEXCEPT ( Table1, Table1[RouteNumber], Table1[WeekNumber] ) ) )Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
