Count Rows based on a value in a column

[clarkey1988]

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

[AlB · Accepted solution]

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 

[clarkey1988]

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.

[AlB]

@clarkey1988 

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