Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
Try something like:
Try something like:
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!!
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
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.'.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |