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...
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

     

     

     

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