Count row values based on two criteria

[clarkey1988]

Hi,

I have a specific route number called Nestle-12 that I invoice $14,100 each week. I need to divide this $14,100 by the number of orders for Nestle-12 for that given week.

So the 1st example below would be $14,100/9 = $1,566.67 each row.

The second example is week 48 where only 4 orders ran for Nestle-12 so would be $14,100/4 = $3525 per row.

How would I write this in DAX?

RouteNumber	StopAK	ord_hdrnumber	LegHeaderEndDate	Week Number	Nestle Revenue	Nestle Week 40 Order Numbers	Split Revenue
NESTLE-12	14376756	2136586	9/28/2020 3:09	40	$                         14,100	9	$        1,566.67
NESTLE-12	14376748	2136585	9/28/2020 5:15	40	$                         14,100	9	$        1,566.67
NESTLE-12	14371915	2135819	10/1/2020 6:29	40	$                         14,100	9	$        1,566.67
NESTLE-12	14355175	2133220	9/28/2020 2:13	40	$                         14,100	9	$        1,566.67
NESTLE-12	14355163	2133214	9/28/2020 4:04	40	$                         14,100	9	$        1,566.67
NESTLE-12	14355103	2133205	9/28/2020 1:45	40	$                         14,100	9	$        1,566.67
NESTLE-12	14402263	2140882	9/30/2020 6:42	40	$                         14,100	9	$        1,566.67
NESTLE-12	14402261	2140881	10/1/2020 5:00	40	$                         14,100	9	$        1,566.67
NESTLE-12	14402253	2140877	10/1/2020 4:30	40	$                         14,100	9	$        1,566.67

RouteNumber	StopAK	ord_hdrnumber	LegHeaderEndDate	Week Number
NESTLE-12	14573180	2168249	11/27/2020 6:45	48	$                         14,100	4	$              3,525
NESTLE-12	14570593	2167816	11/27/2020 5:45	48	$                         14,100	4	$              3,525
NESTLE-12	14570585	2167812	11/27/2020 3:45	48	$                         14,100	4	$              3,525
NESTLE-12	14550807	2164680	11/27/2020 4:40	48	$                         14,100	4	$              3,525

[Ashish_Mathur · Accepted solution]

Hi,

If you want that as a calculated column, then simply divide the second last column by the third last column.

[littlemojopuppy]

Hi @clarkey1988 

Try this...

Calculated Split Revenue = 
    VAR NestleRevenue =
        CALCULATE(
            AVERAGE('Nestle Revenue'[ Nestle Revenue ]),
            FILTER(
                ALL('Nestle Revenue'),
                'Nestle Revenue'[Week Number] = SELECTEDVALUE('Nestle Revenue'[Week Number])
            )
        )
    VAR WeeklyOrders =
        CALCULATE(
            DISTINCTCOUNT('Nestle Revenue'[ord_hdrnumber]),
            FILTER(
                ALL('Nestle Revenue'),
                'Nestle Revenue'[Week Number] = SELECTEDVALUE('Nestle Revenue'[Week Number])
            )
        )
    RETURN

    DIVIDE(
        NestleRevenue,
        WeeklyOrders,
        BLANK()
    )

[littlemojopuppy]

So I went a little beyond what you asked for...a measure to get the weekly order count would be the second variable...

WeeklyOrders =
        CALCULATE(
            DISTINCTCOUNT('Nestle Revenue'[ord_hdrnumber]),
            FILTER(
                ALL('Nestle Revenue'),
                'Nestle Revenue'[Week Number] = SELECTEDVALUE('Nestle Revenue'[Week Number])
            )
        )

[ToddChitt]

Create a specific Measure for the count of these rows, filtered for NESTLE-12:

NESTLE-12 COUNT = CALCULATE ( COUNTROWS ( 'TableName' ), [RouteNumber] = "NESTLE-12" )

This measure can still be sliced by weeks or any other dimension.

Now create the main calculation:

Split Revenue = DIVIDE ( [Nestle Revenue], [NESTLE-12 COUNT] )