Count value in columns

[Rico56]

Hello,

I have something like this:

Client	col1	col2	col2
c1	OK	OK	OK
c2	OK	OK	CRIT
c3	WARN	WARN	OK
c4	OK	OK	OK
c5	OK	CRIT	OK
c6	WARN	OK	OK
c7	OK	OK	OK
c8	WARN	OK	WARN
c9	OK	OK	OK

I try to make a calculated columns to have :

col1	total
OK	6
WARN	3
CRIT	0

col2	total
OK	7
WARN	1
CRIT	1

col3	total
OK	7
WARN	1
CRIT	1

I'm a beginner with Power BI.

Thanks in advance for your help.

[Rico56]

Many thanks for all replies.

Now it works. I have many things to learn about Power BI.

[smpa01]

@Rico56  it can done tyhis way by using M

let
    Source = Web.BrowserContents("https://community.powerbi.com/t5/Desktop/Count-value-in-columns/m-p/926208#M443898"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > TR > :nth-child(1), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > * > TR > :nth-child(1)"}, {"Column2", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > TR > :nth-child(2), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > * > TR > :nth-child(2)"}, {"Column3", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > TR > :nth-child(3), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > * > TR > :nth-child(3)"}, {"Column4", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > TR > :nth-child(4), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > * > TR > :nth-child(4)"}}, [RowSelector="DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > TR, DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > * > TR"]),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Client", type text}, {"col1", type text}, {"col2", type text}, {"col2_1", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"col2_1", "col3"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Client"}),
    Custom1 = Table.DemoteHeaders(#"Removed Columns"),
    #"Changed Type2" = Table.TransformColumnTypes(Custom1,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type2"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Transposed Table", {"Column1"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns1", {"Column1", "Value"}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

[amitchandak · Accepted solution]

Please check the solution at :https://www.dropbox.com/s/pzine68dy2tax7k/clientcount.pbix?dl=0

[MariusT]

Unless you absolutely need it to be in a calculated column, you could drop col1/col2/col3 twice into the visual.

If you choose a table visual, drop the same column in the Value-well twice. The first one will show the distinct values in the column, and for the second one you choose the drop-down button on the column name in the Values-well, and choose Count.