Count of values in new table multiple columns

[abhi139]

Hi,

I have below table in power BI

           MatchA MatchB MatchC

Row1  SAME DIFFERENT BLANK

Row2  SAME SAME BLANK

Row3  SAME SAME DIFFERENT

I need to create report output like below:

Sr.       SAME DIFFERENT BLANK

MatchA 3     0              0

MatchB 2      1               0

MatchC 0      1            2 

Please advise how can I achieve this in power BI and also the option to drill down to records based on criteria (e.g. Clicking on 3 will show all 3 records and so on)

Thanks for your support. 

[v-xulin-mstf · Accepted solution]

Hi @abhi139

Try this in query editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsovN1TSUQp29HUFUi6ebm6uQa5+IUC2k4+jn7dSrA5YjRFCDZRCkTbGkEaYFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ROW = _t, MatchA = _t, MatchB = _t, MatchC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ROW", type text}, {"MatchA", type text}, {"MatchB", type text}, {"MatchC", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ROW"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Try measure as:

Measure 2 = 
CALCULATE(
    COUNT('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Attribute]=MAX('Table'[Attribute])
))

 Here is the output:

The pbix file is attached.

Best Regards,
Link

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[VijayP]

@abhi139 

I suggest to unpivot the Table in Power Query and create a table to get the result 

follow this video to understand that https://drive.google.com/file/d/1Yk_neCzTsKlJFMGRVuRqVz4fFOG9wAJl/view?usp=sharing

[abhi139]

HI @VijayP 

Many thanks for the quick solution - this is very close to what i was looking for.

However, Is there any way i can drill -down in the matrix to see actual data? i.e. if i click/drill down on 2 (Blanks) for Match C, it shows the Row1 and Row2 table record?

Actually Match columns are the comparison between other columns (i didnt added those columns in my oriingal question).

the objective is to show the differences (count of SAME,BLANKS,DIFFERENT) as well as ability to view the records of the count shown in the matrix.

Many thanks once again.