Percentage Column

[macgeorge]

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

[ronrsnfld · Accepted solution]

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.

[ronrsnfld]

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

[macgeorge]

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.

[ronrsnfld]

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.

[macgeorge]

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.

[ronrsnfld · Accepted solution]

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.

[macgeorge]

Nearly there...I am getting a syntax error during parsing on this:

% = ('Tiers 1 & 2 - Outcomes)[Total_Compliant]/[Spot Checks Totals]

[ronrsnfld]

You probably need to remove the parentheses:

% = 'Tiers 1 & 2 - Outcomes[Total_Compliant]/[Spot Checks Totals]

but I don't think you need the table specification at all, since the column is in the same table as your data.

What happened when you used the formula as I provided it, without the table name?

[macgeorge]

I kept the table name in there as there are other tables. Excellent work. I was clearly over complicating things.Its working perfectly now thanks to your help, guidance and most of all patience. 

[macgeorge · Accepted solution]

Got it!

It was my fault. I've corrected it now. It reads:

% = ('Tiers 1 & 2 - Outcomes'[Spot Checks Compliant]/[Spot Checks Totals])

[macgeorge]

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. 

[macgeorge]

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