Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

 

Brz_Orçado

 

To get the sum of the total budgeted for employee, I created a measure using CALCULATE and SUMX:

 

Total Orçado = CALCULATE(SUMX('Brz_Orçado','Brz_Orçado'[Value]))
 
The formula works perfectly. And I can filter by employee ID and per month without any difficulty using the slicer.
 
But my "problem" begins now...
 
I have 3 more tables:

 

Arg_Orçado

Col_Orçado

Per_Orçado

 

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.

db_Geral

1 ACCEPTED SOLUTION
hnguy71
Memorable Member
Memorable Member

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.

 

multiple_selecton.PNG

 

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])

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

View solution in original post

3 REPLIES 3
hnguy71
Memorable Member
Memorable Member

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.

 

multiple_selecton.PNG

 

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])

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!
Anonymous
Not applicable

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?

^_^ 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).



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.