Forum Discussion

rpatel1's avatar
rpatel1
New Member
8 years ago
Solved

Create ratio from two sources

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,

3 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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's avatar
      rpatel1
      New Member
      8 years ago

      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