Skip to main content
cancel
Showing results for 
Search instead 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

Reply
ssutton
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
amitchandak
Super User
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])

View solution in original post

2 REPLIES 2
amitchandak
Super User
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])

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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