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...
FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
1 year ago

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)