topic Re: Exclude rows from measure in DAX Commands and Tips

Exclude rows from measure

analyser — Tue, 10 Dec 2024 20:24:53 GMT

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!

Re: Exclude rows from measure

Semaj06Fr — Tue, 10 Dec 2024 21:03:09 GMT

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
result

Re: Exclude rows from measure

analyser — Tue, 10 Dec 2024 21:31:03 GMT

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

Re: Exclude rows from measure

Semaj06Fr — Tue, 10 Dec 2024 21:43:54 GMT

I wuld need an exemple of your data model with cleaned datas to help you

Re: Exclude rows from measure

Poojara_D12 — Wed, 11 Dec 2024 05:38:34 GMT

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

Re: Exclude rows from measure

FreemanZ — Wed, 11 Dec 2024 05:54:01 GMT

Hi 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 _result

it worked like:

For profit, i use (revenue-expense)/expense. so it was ((123+15 + 23) - (2525 + 84)) / (2525 + 84)

Re: Exclude rows from measure

Anonymous — Thu, 12 Dec 2024 07:48:23 GMT

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