Forum Discussion
Percentage Column
Hi I am struggling with percentages in power bi - i have tried many combinations and none of them are correct. I have a table that I want to provide percentage of compliance in the % column:
| PRODUCT | Spot Checks Totals | Total_Compliant | % |
| PRODUCT 1 | 10 | 6 | |
| PRODUCT 2 | 24 | 0 | |
| PRODUCT 3 | 2 | 2 | |
| PRODUCT 4 | 12 | 12 | |
| PRODUCT 5 | 2 | 2 |
Is there a good way to produce percentages in power bi for the above column please?
Thank you
As I indicated in my code comments, you only need to change the "Source=" line to reflect your actual data source. What happened when you tried it? M-code is M-code and can use multiple data sources.
Another option, in Power BI, would be to use a Measure (add a column).
% = [Total_Compliant]/[Spot Checks Totals]and, again, set the data type to percentage (here you'd use a dropdown in the Column Tools menu to set the cell format.
Got it!
It was my fault. I've corrected it now. It reads:
% = ('Tiers 1 & 2 - Outcomes'[Spot Checks Compliant]/[Spot Checks Totals])
11 Replies
Just one thing - i am trying to do the % of each line for example product 1 has had 10 spot checks whereby 6 were compliant - the percentage for that would be 60% compliant
The table I am working on looks likes this (in Excel):
In Excel the formulae is simple: =SUM(C2/B2*100)
I am trying to to wrack my brains (failing) to work out how to do this in DAX that will provide the percentage for each row. The percentage should show how many spot checks are compliant against the spot check done for that product.Any help please would be gratefully appreciated.
Just divide Total_Compliant by Spot Checks Total
eg
let //change next line to reflect actual data source. Source = Excel.CurrentWorkbook(){[Name="Compliance"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{ {"PRODUCT", type text}, {"Spot Checks Totals", Int64.Type}, {"Total_Compliant", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [Total_Compliant]/[Spot Checks Totals], Percentage.Type) in #"Added Custom"Personal preference but, if I have a column showing a percentage, I prefer to leave it as such instead of multiplying by 100
Original data
Results
Thank you for this.
Would the code look any different if the source is in Power BI already, I may have mislead you that I am working from Excel.
I provided the example from Excel to explain what output I need in Power BI.
Again, apologies, I am a novice here.As I indicated in my code comments, you only need to change the "Source=" line to reflect your actual data source. What happened when you tried it? M-code is M-code and can use multiple data sources.
Another option, in Power BI, would be to use a Measure (add a column).
% = [Total_Compliant]/[Spot Checks Totals]and, again, set the data type to percentage (here you'd use a dropdown in the Column Tools menu to set the cell format.
I reread your feedback and I have tried to address this with the source in Power BI
The table in Power BI is called "Tiers 1 & 2 - Outcomes". The columns in that table are exactly as above.
Am I using a Measure to do this calculation? If so, can you please write this for me, as I am getting lost. Sorry.
Read my edited previous response which now provides a DAX solution.
My first response was M-Code which is in Power Query which you get to by selecting Transform Data from the Home tab of Power BI.
My second response includes a DAX solution which you enter using "New Column" from the Table Tools or Column Tools tab.