Sum of Multiple columns and then %

[Anonymous]

Trying to add multiple rows and then divide against other multiple rows to get %.  I have zero issues creating this formula in Excel Power Pivot but for some reason, this is thowing errors at me.

Essentially, the sum of all the Red Columns / sum of the green columns.   

[Anonymous · Accepted solution]

Figured it out.............For some reason, the custom columns defaulted the format to TEXT instead of Number and was summarizing.....😥

[sidneyjacobs]

You don't have to do the transformations in Excel. Everything i wrote above can be done in PowerBI.

I will try to explain the steps more clear. If you have your data from Excel loaded into PowerBI go to "home" in the top left corner. Choose "Transform data". This will open a new window. In this new window select your table on the left side and go to the tab called "Add column" in the top left. Choose the option "Custom Column".

Once you have done all that, you should see a window like this:

Here you can click on the columns you want on the right side, and add them up. Once you have done so for Total Created and Total Resolved you have to make one more conditional column like so:

This gives you the percentage of Created/Resolved per row.

[ryan_mayu]

@Anonymous 

What's solution you are using? What's the error you are encountering?

Do you want to create the % for each row or create a measure to calculate?

[sidneyjacobs]

Hey Brian,

If you go to transform data

and then pick your table, you can add a new column by going to 'Add column' and choosing 'Custom column'

you can then simply make 1 column, say Green: being [chat created]+[phone created]+...
Then you can make a second column, say Red: adding the red columns together.

finally make a third column that says Green/Red

Hopefully that gives the solution you were looking for.

[Anonymous]

So , the data source is an excel sheet and I tried that in the excel sheetdirectly once and just now via the method you just showed me....This is what happens...

The individual day values will display correctly in the view...but when they are added by date, its all jacked up

and then when I create a simple formula dividing them, the results is always 1.00 as a %.