matrix - Calculate and show % of total of 1 category in powerbi

Question

Please can you assist, I have a demographic table in power bi and I am required to only show the % of the total for 1 of those demographics , below is the table view I require.

I have included a data sample, I would just pivot that/use a matrix, and count the ethnic group

enter image description here

enter image description here

question from:https://stackoverflow.com/questions/65931819/calculate-and-show-of-total-of-1-category-in-powerbi

Answer 1

Follow these below steps to achieve your required output-

Spet-1: Duplicate your current data table and give an appropriate name. You can now apply necessary transformations to this new table and your source data is unchanged for other uses. Let your data is as below-

Step-2: Go to Advanced Editor of the new table and replace the code with this below code-

Note: your table column names need to be as shown in the above image.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxNLcpMTlTSUXIEYr/8PIXwjMySVKVYHaBcaXFJUWJOJkjWCYiRZNKgmpwwNcENdMZrIBbr4DrRLENzBpquNNzWwQ10wWmgC0WOxK3TGXt4YbEuDbunSQhGkoMKl1VYvEtMRJMUEvjDHk84obiSqkkRfxogPiZxuRC7YbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Area = _t, Client = _t, #"Ethnic Group" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Area", type text}, {"Client", type text}, {"Ethnic Group", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Client", "Area", "Ethnic Group"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[#"Ethnic Group"]), "Ethnic Group", "Count", List.Sum),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"Non White"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"White"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value1", "Custom", each ([Non White]*100.00)/([Non White]+[White])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Custom", "% Non White"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns1", {"Client", "Area"}, "Attribute", "Value"),
    #"Renamed Columns2" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Ethenic_group"}}),
    #"Replaced Value2" = Table.ReplaceValue(#"Renamed Columns2","Non White"," Non White",Replacer.ReplaceText,{"Ethenic_group"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","White"," White",Replacer.ReplaceText,{"Ethenic_group"}),
    #"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","%  Non  White","% Non White",Replacer.ReplaceText,{"Ethenic_group"}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value4", "Custom", each if [Ethenic_group] = "% Non White" then Text.From([Value]) & "%" else Text.From([Value]))
in
    #"Added Custom1"

Here is the final output-

Step-3: Now get back to report and add a Metrics in the report and configure as below-

Step-4: Click on the shown Red boxed icon as below-

Finally you will have this below presentation-