cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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.

1 ACCEPTED SOLUTION
Super User

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 2
Super User

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])

Frequent Visitor

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors