Create Multiple Calculated Columns in Transform or DAX at once

[perryadams]

Hello,

I am trying to do a loop in Power BI since I have a dataset with multiple categories. Each category has it's own set of columns. In this example, there are categories A, B, and C, and each category has a numerator and denominator column. I would like to create a column in transform or DAX for each cateogry that calculates the ratio. In reality there are 20 categories and I have multiple calculated columns for each category I need to create. I am trying to reduce the manual steps needed. Below is an example of the table I'm trying to create without having to manually make 3 columns. Thanks!

[perryadams]

Thanks! Makes sense that an unpivoted data structure would make measure creation much simpler. Do you know what the tradeoffs would be in performance? If I have 20 categories, in the current format I'd need 20 measures. If the data was unpivoted, the number of rows would increase by 20x but only 1 measure would be needed

[Corey_M]

tbh, I'm not really the person to ask about performance, I've never really run into issues where power Bi took more than a second to load or update a visual to have to try and optimize performance

[Corey_M · Accepted solution]

Sounds more like a data format issue than anything else, you can get it into a form that is much easier to calculate on by following these steps in Power Query

Step 1: Unpivot

Step 2  Split the Attribute

Step 3: Repivot by Attribute.1 

Step 4: add your division column

yeah it might have been faster to do it manuall lol, hope this helped