Sum Columns with Field Parameters

[Alan_irt]

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?

[Anonymous · Accepted solution]

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]

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.

[Anonymous]

Hi @Alan_irt ,

What's your table like:

Or 

Best Regards,
Eyelyn Qin

[Alan_irt]

1st table version.

[Anonymous]

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]

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