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

[Anonymous]

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

[Anonymous · Accepted solution]

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.

[Anonymous]

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!!

[JustJan]

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 

[Anonymous]

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.'.