Forum Discussion

analyser's avatar
analyser
New Member
1 year ago

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 # Co...
Semaj06Fr's avatar
Semaj06Fr
Frequent Visitor
1 year ago

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
result

analyser's avatar
analyser
New Member
1 year ago

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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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