Forum Discussion

Ash187's avatar
Ash187
Frequent Visitor
6 years ago
Solved

Slicer on column

I would like to have a slicer that would sum columns based on the selected column names, whether A1, A2 or A3 (with multiple selections allowed). As an example I have a table below with A1, A2 and A3 columns. The expected slicer would have choices of A1,A2 and A3. How can I add slicer on columns itself instead of its values?

As an example, I would like to plot A1 sum vs ID or A1+A2 sum vs ID based on selecting A1,A2 or A3 or combination of two or more in slicer or something allowing equivalent functionality.

 

Thanks

 

 ID              A1             A2             A3

1515750
2992393
3922811
4134381
5982147
1315798
2251128
3986188
4268538
5298990
Need Help
Show and Tell
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    Hi Ash187 

     

    Please kindly check whether you'd like to get below results:

    1. here's the source data Table (3):

    2. Add below M code to unpivot the columns:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY67EcAgDEN3cU0RbD5mFi77rxE/c6HRCUlI3luqFOkJE3jkLVs06FoBajBLMSmOekCtKTYoTgP8iJ0kIaW4zRShdofC/oe0n7osvkN8H4jud0gHb+Lmd0i504EVx78f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, A1 = _t, A2 = _t, A3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"A1", Int64.Type}, {"A2", Int64.Type}, {"A3", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

    3. Then add the calculated table:

    Table 2 = CALCULATETABLE(ALLSELECTED('Table (3)'),FILTER('Table (3)',[Attribute]="ID")) 

    4. Add the measure to generate the results:

    Measure 4 = var a = SUMX('Table (3)',[Value])
var b = SUMX('Table 2',[Value]) 
Return
a-b

     

     

     

1 Reply

  • v-diye-msft's avatar
    v-diye-msft
    Community Support
    6 years ago

    Hi Ash187 

     

    Please kindly check whether you'd like to get below results:

    1. here's the source data Table (3):

    2. Add below M code to unpivot the columns:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY67EcAgDEN3cU0RbD5mFi77rxE/c6HRCUlI3luqFOkJE3jkLVs06FoBajBLMSmOekCtKTYoTgP8iJ0kIaW4zRShdofC/oe0n7osvkN8H4jud0gHb+Lmd0i504EVx78f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, A1 = _t, A2 = _t, A3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"A1", Int64.Type}, {"A2", Int64.Type}, {"A3", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

    3. Then add the calculated table:

    Table 2 = CALCULATETABLE(ALLSELECTED('Table (3)'),FILTER('Table (3)',[Attribute]="ID")) 

    4. Add the measure to generate the results:

    Measure 4 = var a = SUMX('Table (3)',[Value])
var b = SUMX('Table 2',[Value]) 
Return
a-b