Exclude rows from measure

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