Forum Discussion
Exclude rows from measure
I have a large transactions table (600M+ records). The table has both revenues and expenses records.
I have a measure that calculate a profit % = (revenue - expense) / revenue
| Record # | Contract | Transaction Type | Area ID | Product ID | Amount $ | .... |
| 1 | 1 | Revenue | 151 | 98 | $123 | |
| 2 | 1 | Expense | 151 | 98 | $2525 | |
| 3 | 1 | Expense | 151 | 98 | $232 | |
| 4 | 2 | Revenue | 54 | 874 | $15 | |
| 5 | 2 | Revenue | 54 | 874 | $23 | |
| 6 | 2 | Expense | 54 | 874 | $84 | |
| 7 | 3 | Revenue | 19 | 80 | $0 | |
| 8 | 3 | Expense | 19 | 80 | $123 | |
| 9 | 3 | Expense | 19 | 80 | $646 | |
| .... |
I want to exclude all records from my profit % measure if the sum of revenue for a contract is zero. For example, I want to exclude records 7-9 from my measure. How??
Thank you!
6 Replies
I wuld need an exemple of your data model with cleaned datas to help you
Hi
Please try this code
Profit% =
VAR _contract =
SELECTEDVALUE ( 'Table (2)'[Contract] )
VAR Rev =
CALCULATE (
SUM ( 'Table (2)'[Amount $] ),
'Table (2)'[Transaction Type] = "Revenue"
)
VAR exp =
CALCULATE (
SUM ( 'Table (2)'[Amount $] ),
'Table (2)'[Transaction Type] = "Expense"
)
VAR result =
IF ( Rev <> 0, DIVIDE ( Rev - Exp, Rev, 0 ) )
RETURN
resultI get a blank result when I try this.
I think because all my of visuals are not at for contracts? I have a bunch of tables that are joined to the transactions table. For example I have a regions table that is joined to the transactions table above using the contract field. I want to aggregate the profit calc % by region.
Hi analyser ,
Could you tell me if your issue has been solved? If it is, kindly Accept the helpful reply as the solution.
Or you can share your workaround and mark it as the the solution. Then more people will benefit from it.Or you are still confused about it, I think you issue should be caused by your data model.
Please share us with a sample file without sensitive data. You can also show us a screenshot with the result you want.This will make us easier to find the solution.
Best Regards,Rico Zhou
- Poojara_D12
Super User
Hi analyser
To exclude records where the sum of revenue for a given contract is zero, you can modify your Profit % measure to check for this condition. Specifically, you'll need to use a DAX formula that ensures that only contracts with a positive revenue are included in the calculation. Here’s how you can adjust your DAX measure:
Profit Percentage = VAR TotalRevenue = CALCULATE( SUM(Transactions[Amount $]), Transactions[Transaction Type] = "Revenue" ) VAR TotalExpense = CALCULATE( SUM(Transactions[Amount $]), Transactions[Transaction Type] = "Expense" ) RETURN IF(TotalRevenue = 0, BLANK(), (TotalRevenue - TotalExpense) / TotalRevenue)The BLANK() function causes Power BI to ignore these records, ensuring that they do not affect your visualizations.
This approach efficiently handles large datasets because the filter is applied at the contract level, reducing the impact of irrelevant data.
This solution should work for your case, as it ensures that contracts with zero revenue are excluded from your profit percentage calculation.
Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂
Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 
FreemanZHi analyser ，
not sure if i really get you, try like:
measure = VAR _table = FILTER( ADDCOLUMNS( SUMMARIZE( data, data[contract] ), "Revenue", CALCULATE(SUM(data[Amount $]), data[transaction type] = "Revenue"), "Expense", CALCULATE(SUM(data[Amount $]), data[transaction type] = "Expense") ), [Revenue]<>0 ) VAR _result = SUMX(_table, [Revenue])/SUMX(_table, [Expense]) -1 RETURN _resultit worked like:
For profit, i use (revenue-expense)/expense. so it was ((123+15 + 23) - (2525 + 84)) / (2525 + 84)
