Forum Discussion
Create a table where each row has a specific formula
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.
I ended up creating columns for each check and then more columns for each summary and suming them up from all the entries.
6 Replies
Forgot to say, but if anyone needs clarification or needs further details, don't hesitate to ask.
- djurecic
Super User
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?
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?
- djurecic
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.
