Create a table where each row has a specific formula

[Help_Required]

I have used power query for years and I am trying to move to power bi. I have a view I created in excel that I am trying to replicate in power bi, where we count the # of projects that are compliant in a specific area. This is done using countifs, its done in a specific cell and I have 7 unique formulas for the 7 areas. I then use that and subtract from total to give me the non-compliant. I then use formula to generate % compliance:

In Power Bi I am struggling to get my head around how to store the data in order for it to be output in the same format, I have tried all sorts but it just doesn't come out in a useable way. Anyone have any ideas how to aproach this?

The data is stored in a table with a line for each project and calculated formulas in each column to determine compliance in each area.

[djurecicK2]

Hi @Help_Required ,

 I'm not totally sure what you are looking for. Are you asking how to replicate the excel formulas in each column to determine compliance?

[Help_Required]

So in excel I can create a table and use different formulas for each line of the column to give summary calculation but don't seem to be able to do that in power bi everything has to be calculated the same way in a single column, so take one of the section, "status reporting" this is claculated by counting the number of projects that have a certain value in the reporting compliance column but the "issues and risks" is a count for a diferent column. I have 6 different columns to count but only 1 formula. Hope this makes sense?

[djurecicK2]

@Help_Required ,

 Yes, this makes sense. Yes, you would probably need to combine the logic using IF or SWITCH statments in DAX, or look at doing transformations in Power Query.

Or, you could keep the excel spreadsheet as is and use it as the datasource for your Power BI report.

[Help_Required]

Not sure what you mean, I have tried all sorts to date, looked at counting through meaures, creating column for each check, even looking to create a a table with list of the column names and tring to make that a variable. To no avail, surely I cannot be the only person having this issue, but maybe I need to start fresh, trying to replicate maybe my issue, what do people suggest I need to do to arrive at this type of view? What should my raw data table look like?

[Help_Required · Accepted solution]

I ended up creating columns for each check and then more columns for each summary and suming them up from all the entries.

[Help_Required]

Forgot to say, but if anyone needs clarification or needs further details, don't hesitate to ask.