Converting excel columns that leverage formulas to powerBI

[Tylerjknapp]

Hello, 

I have a current excel workbook that uses formulas to calculate columns, these formulas use values from other sheets inside the workbook. I am trying to get some guidance on how I can convert this excel workbook into a PowerBI Report. I know I have to flatten the data but should I use measures of calculated columns? Some of the formulas from excel are - 

=SUM(COUNTIF('Computer 1'!$F:$F,">15")+(COUNTIF('Computer 2'!$F:$F,">15"))) 

which I converted to this - 

Computer Data Sources < 15 = COUNTAX(Filter('Computer 1', 'Computer 1'[Score Data Sources] < 16), 'Computer 1'[Score Data Sources]) + COUNTAX(Filter('Computer 2', 'Computer 2'[Score Data Sources] < 16), 'Computer 2'[Score Data Sources])

Now my question is because the summary page is set up something like this - 

How do I have the calculated column pull the data from the correct table and can I have data pulled from one table to another? Rows 1, 9, 17, 24, and 31 are all different tables. I've made measures that are displaying the same data as in excel but how do I get it display like below -
 

[PaulDBrown]

So the actual source is a single table including the data from all 5 sheets? I'm not at my PC now (it's 20:36), but from what I saw, both computer tables have the same structure, same for the database tables, and the printer table differs from the other 4 in the fields...

[Tylerjknapp]

Yes, it's from one table, due to excel constraints on the number of rows it was split up into multiple tables. The column Letter can be removed it was just to identify that row as I had to remove the name of the actual device. Class is just to identify what class it is if it's a database or printer, but all of them have these 4 columns - 

Score Attribute Completeness

Score CI Currency

Score Data Sources

Score Ownership

with some also having 

Score Relationship Expected 

[PaulDBrown]

Sorry, I lost track of this thread... I'm going to need help understanding the fields in the sample data.
Which fields are common to two or more tables? I take it that the two computer tables are related and both database tables are related?

Do the Letters (A, B, C...) have any particular equivalence between the tables?

[Tylerjknapp]

No, so ultimately this is going to be coming from one table, currently we export the data into seperate reports (sheets) which ends up being seperate tables. But the source of this data is one table, that one table has all the classes and the different scoring columns. What I'm having a hard time time trying to figrue out how to do is how to make it look like the summary page in PowerBI, like a spreadsheet where each cell is a calculated measure. In the data I sent over, each table should have these 4 columns - 

Score Attribute Completeness

Score CI Currency

Score Data Sources

Score Ownership

[PaulDBrown]

You can either post data in a message or by sharing a link to a cloud service ((OneDrive, Google Drive, Dropbox...)

[Tylerjknapp]

Just following up if any progress was made with this. I am thinking I am going to have to make a new table that has calculated columns for each measure/formula

[Tylerjknapp]

Hi PaulDBRown, 

Let me know if this link works for getting the sample data set - 

Sample Data Set 

[Tylerjknapp]

Ok thank you, I will upload a link in an hour or two when I am done with work and can upload the sample data to my google drive account. 

[Tylerjknapp]

There are 4 columns that each table has called 

Score Attribute Completeness

Score CI Currency

Score Data Sources

Score Ownership

 I will work on getting you a sample dataset

[PaulDBrown]

How is your model set up? What is the structure of each fact table? 
which fields are common to 2 or more tables?

if possible, provide sample data (not images please), and a depiction of the expected output.

[Tylerjknapp]

I've put together some sample data. How can I get you the sample data? Can I just post it here?