Forum Discussion

Alan_irt's avatar
Alan_irt
New Member
4 years ago
Solved

Sum Columns with Field Parameters

Hi, I am sure this is really simple but haven't been able to find a simple answer to this.

 

I have a table called test data

 

I want to allow a user to select any combination of field to be shown in a new visual table.

I've created field parameters and set them as a slicer on the page, allowing a user to choose which columns to display.

I would like to add a field in the table which sums the values shown in the filtered table. So in the above example 8 (1+3+4).

Any ideas?

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Alan_irt ,

     

    Since A,B,C,D are column names not Values, we could not apply them to slicer to controll their visibilty. And Table visual could not realize your expected output. We need to use measures and Matrix visual instead.

     

    Please firstly create a new table for slicer:

    Then create measures:

    Matched Value = 
IF(MAX('For Slicer'[Value])="A", SUM('Table'[A]),IF(MAX('For Slicer'[Value])="B", SUM('Table'[B]), IF(MAX('For Slicer'[Value])="C",SUM('Table'[C]),IF(MAX('For Slicer'[Value])="D",SUM('Table'[D])))))
    Result = 
var _t=SUMMARIZE('For Slicer',[Value],"sum",[Matched Value])
return SUMX(_t,[sum])

    Output:

     

     

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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Alan_irt ,

     

    According to your screenshots, it seems that you used Matrix visual to display values.

    So please firstly check the data type is Number and then go to Format pane, enable Column subtotals option:

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

    • Alan_irt's avatar
      Alan_irt
      New Member
      4 years ago

      Thanks Evelyn, I'll give this a try. Is there a way to do this in a formula? I would ideally have this displayed in a table rather than a matrix so I can have more info in the columns. i.e.

       

      ID    /   Address           /    Model /   A  /  B  /  C  /   D   /   Sum

      1      /  1 Smith Street  /   Sample/   1  /  2  /  3 /   4    /   10

       

       where A/B/C/D visibilty are controlled by the slicer and Sum adds up the displayed values from A/B/C/D

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Alan_irt ,

     

    What's your table like:

    Or 

     

    Best Regards,
    Eyelyn Qin

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Alan_irt ,

     

    Since A,B,C,D are column names not Values, we could not apply them to slicer to controll their visibilty. And Table visual could not realize your expected output. We need to use measures and Matrix visual instead.

     

    Please firstly create a new table for slicer:

    Then create measures:

    Matched Value = 
IF(MAX('For Slicer'[Value])="A", SUM('Table'[A]),IF(MAX('For Slicer'[Value])="B", SUM('Table'[B]), IF(MAX('For Slicer'[Value])="C",SUM('Table'[C]),IF(MAX('For Slicer'[Value])="D",SUM('Table'[D])))))
    Result = 
var _t=SUMMARIZE('For Slicer',[Value],"sum",[Matched Value])
return SUMX(_t,[sum])

    Output:

     

     

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

    • Alan_irt's avatar
      Alan_irt
      New Member
      4 years ago

      Thanks Evelyn, I'll give this a go. I was planning on using the new Field Parameter slicer option to filter by the column names. This appears to work well within the table to control which columns are shown.