Create ratio from two sources

[rpatel1]

Having difficulty connecting data from 2 sources and creating a ratio between the data

Below are my two sources and the columns

Source 1:

Server Name  |   Units of Service

Source 2:

Server Name  |   Hours Worked

I can connect both sources to create a table that will show me the Server name, hours worked and total units of service

I want to go one step further and create a new column that will give me the ratio between units of service and hours worked

How would I create that? DAX expression??

Thanks,

[rpatel1 · Accepted solution]

Found the solution for anyone who comes across this thread...

See website for great info on setting up this column

https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-create-calculated-columns

Basically needed to use the RELATED function to have it point to one column, and have it match with the other column

[v-jiascu-msft]

HI @rpatel1,

1. What does your model look like? Which Source has the unique values?

2. Do you need a new column or a visual?

Maybe you can try the formula below. Can you share a sample?

Ratio = calculate('Source 2'[Hours Worked]) / [Units of Service]

Best Regards,

Dale

[rpatel1]

Hi Dale,

Thanks for the response. Let me try and clarify a little more...

I have two excel spreadsheets (files) with one common column between them (server name)

Source 1 has Server Name matched with Units of Service

Source 2 has Server Name matched with Total Hours worked

I am able to make the relationship between the two, so I can create a table in BI that shows me:

Server Name  |   Units of Service  |   Total Hours

I want to go one step further and create either a new column in the table, or a new visual that will show me the ratio between Total Hours and Units of Service

Each time I try to make a new column with the formula you provided or different formula, I get the following error:  "A single value for Total Hours in table 'Source 2' cannot be determined."

I don't want a single output, instead a formula for the column that will show me each ratio for each provider (multiple rows)

Hope I clarified a bit more, truly appreciate the help...

RP