create slicer for both column and rows

[Anonymous]

hi all

i have a data sheet as follow:

	Blank	Basic	Affiliate	Student	VIP	VVIP
Alabama	20	14	13	8	14	1
Alaska	9	18	11	14	15	1
Arizona	17	17	11	18	16	0

How should i create the slicer for type of membership? Any advice is appreciated. Thank you.

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

I created a sample using two ways. One is creating a new table as a slicer. Another is unpivoting the columns. You could have a try.

• Create a new table manually

Measure = 
VAR a =
    SELECTEDVALUE ( 'Filter Table'[Column] )
RETURN
    SWITCH (
        TRUE (),
        a = "Basic", SUM ( 'Table'[Basic] ),
        a = "Affiliate", SUM ( 'Table'[Affiliate] ),
        a = "Blank", SUM ( 'Table'[Blank] ),
        a = "Student", SUM ( 'Table'[Student] ),
        a = "VIP", SUM ( 'Table'[VIP] ),
        a = "VVIP", SUM ( 'Table'[VVIP] )
    )

• Unpivot columns

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WOrTAMScxKTE38dACJR0lIwMgYWgCIoyBhAWcpxSrA1VbnA1RagkSBiswhKsyRVZalFmVnwdRa2gOJwzh2syAhIFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [State = _t, Blank = _t, Basic = _t, Affiliate = _t, Student = _t, VIP = _t, VVIP = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"State", type text}, {"Blank", Int64.Type}, {"Basic", Int64.Type}, {"Affiliate", Int64.Type}, {"Student", Int64.Type}, {"VIP", Int64.Type}, {"VVIP", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"State"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

 

Best Regards,

Xue Ding

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

[Tad17]

Alternatively, if you don't need the state to be filtered, pivot the data and just create a slicer for membership type.

However, you should be able to create slicers for each field since you have it in a matrix format.

You will need your data formatted correctly first:

State   Membership type   Quantity

Alabama  Blank                     20

Alabama   Basic                     14

Etc.

[amitchandak]

Unpivot the data

https://radacad.com/pivot-and-unpivot-with-power-bi