topic Re: Mulitple duplicates, need sum of total, not line by line. in DAX Commands and Tips

Mulitple duplicates, need sum of total, not line by line.

Anonymous — Sat, 15 Feb 2020 21:08:42 GMT

Hi, I need help! I'm trying to return the value for where there are costs, but no revenues.

But I have multiple duplicate sales orders.

So when I use a =calculate(sum(cost of sales), net revenues=0). I get the result form sales order 1 below as well as sales order 2. I only want the results from sales order 2, where revenue in both instances is 0.

I don't want to aggregate (group) the data in power query because I need other line item details.

Sales Order ID Net Revenue Total Costs


1	0	15
1	30	0
2	0	15
2	0	15

Totals
Sales Order ID	Net Revenue	Total Costs
1	15	15
2	0	30

Re: Mulitple duplicates, need sum of total, not line by line.

Anonymous — Sun, 16 Feb 2020 14:46:31 GMT

hi Anonymous ,

I think you can use something like this:

SalesOrderIDsWithoutRevenue = 
var currentSalesOrder = min(data[SalesOrder])  
var rowsWithRevenue = CALCULATE (                   -- find all rows with revenue for SalesOrderId
COUNTROWS(data),
all(data),
data[SalesOrder] = currentSalesOrder, 
data[Net Revenue] > 0
)
var x = CALCULATE(
sum(data[Total Costs]),
all(data),
'data'[SalesOrder] = currentSalesOrder 
)
return 
if (rowsWithRevenue=0,x)    -- Only show a value when there are no revenues against the SalesOrderID

This will not show a value for SalesOrderId 1.

Jan

Re: Mulitple duplicates, need sum of total, not line by line.

Anonymous — Sun, 16 Feb 2020 17:58:40 GMT

Hi Jan,

Thanks for taking the time to answer me!! I think your solution is probably genius! If only I understood it 🙂

I'm using PowerPivot in Excel. So I tried to translate your solution. I made three formulas as follows:

Current Sales Order=min('Data'[Sales Order ID])

Rows with Revenue=CALCULATE(countrows('Data'),all('Data'),'Data'[Sales Order ID]=[Current Sales Order])

Rev grouped by Sales Order=if([Rows with Revenue]=0,CALCULATE([Total Costs],all('Data'),'Data'[Sales Order ID]=[Current Sales Order]))

The last two formulas have the following error: A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.'.

Re: Mulitple duplicates, need sum of total, not line by line.

Anonymous — Sun, 16 Feb 2020 18:55:59 GMT

Try something like:

CostWhereRevenue0 = SUMX(SUMMARIZE('Table', [Sales Order ID], "Cost", IF (SUM('Table'[Net Revenue]) = 0, SUM('Table'[Total Costs]))), [Cost])

It will group the data by Sales Order ID (that's what summarize does) and then check if net revenue is 0 for each row. If so it sums the cost.

Re: Mulitple duplicates, need sum of total, not line by line.

Anonymous — Mon, 17 Feb 2020 00:10:40 GMT

THANK YOU!! This is awesome! What a beautiful quick way to find get the solution!! Love this!

I didn't realize that you had to wrap the summarize function in a sumx, and that you could reference the summarize virtual table in this! How cool!!

Thanks!!