Forum Discussion
CREATE CALULCATIONS ON VALUES FROM SELECT ROWS
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.
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])
2 Replies
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])
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
