Forum Discussion
Calculate with slicer
Hello all!
Here is my current situation:
I have a table with an employee ID column, a column with the company ID (where the employee works) and 12 columns, representing the months of the year, with the amount budgeted for this employee.
To get the sum of the total budgeted for employee, I created a measure using CALCULATE and SUMX:
And I need to create a slicer using the Company ID.
When I select 2 (Brz), I get the sum of Brz (eg.: 150.000,00). When I select 100 (Arg), I get the sum of Arg (eg.: 250.000,00).
When I select 2 and 100, I need to get the sum of Brz and Arg together (eg.: 400.000,00).
How do I do that?
---EDIT---
The 4 tables are related to db_Geral, which is also related to db_Empresa.
Within your slicer,
make sure it's enabled to take multiple selections. It's on by default. To have take multiple selections, press CONTROL + LEFT CLICK and you should have both or all the selected items.
Two things I'd suggest to change:
1-Append all like tables together so you can utilize 1 measure to sum all locations
2-Your SUMX needs to change to SUM.
Total Orcado = SUM(Append_Locations[Amount])
3 Replies
hnguy71Super User
Within your slicer,
make sure it's enabled to take multiple selections. It's on by default. To have take multiple selections, press CONTROL + LEFT CLICK and you should have both or all the selected items.
Two things I'd suggest to change:
1-Append all like tables together so you can utilize 1 measure to sum all locations
2-Your SUMX needs to change to SUM.
Total Orcado = SUM(Append_Locations[Amount])
Hey hnguy71, thanks for the reply
I ended up using Append and It really worked. I got a spreadsheet with almost 2kk of rows hahaha
But why should I use SUM instead of SUMX?
- hnguy71
^_^ glad that worked out for you!
And since you're only doing a basic sum of total without filter context or additional column aggregations the basic sum will work. In addition, SUMX runs down every single row at a time, parses it, stores it in temporary memory, then sums your total, and finally releases the memory. You mentioned that you have almost 200k of rows. Imagine a larger dataset with 10 million rows. That could potentially be a bottleneck if you're using the wrong function. Lastly, since it's a measure, it recalculates every time there's a change to your visuals.
Final note, you should drop your outer CALCULATE because you're not modifying the summation with any specific filters.... yet (unless you plan to later on).