CREATE CALULCATIONS ON VALUES FROM SELECT ROWS

[ssutton]

I need to create a measure that DIVIDES the SUM of select rows in column A by the SUM of select rows in column B.

Amount of Overhead Cost + Amount of PTO = TOTAL COST

The TOTAL COST will be used in a measure to calculate percentage of cost to Revenue

My data is in SQL Database and is structured like this:

Date       Office     Column A              Column B      Amount
Jan-20    1234      Revenue                 Revenue           300
Jan-20    1234      Overhead Cost       Office                25
Jan-20    1234      Direct Expense        Mileage           100
Jan-20    5678      Revenue                 Revenue           300
Feb-20   5678      Overhead Cost        PTO                   25
Feb-20    5678     Direct Expense        Labor               100

I can provide a sample excel file with data shema and values via one drive.  The message will not post if I format the above data into a table. 

[amitchandak · Accepted solution]

Try new measures like

cost = calculate(sum(table[Amount]),table[Column A] in{"Overhead Cost","PTO"})
revenue = calculate(sum(table[Amount]),table[Column A] ="Revenue")

Ratio =divide([cost],[revenue])

[ssutton]

Thank you for your quick response.  This is very close, but the cost formula doesn't account for the "PTO" value being located in a different column,

cost = calculate(sum(table[Amount]),table[Column A] in{"Overhead Cost","PTO"})

 sum table amount Column A + sum table amount Column B