Sum result using multiple slicers from same column

[LPenatti]

Hi,

I'm creating a report to calculate the budget of installations the company have to do after the sale.

My data is composed of 5 tables:

- Technician/Equipment
- Product
- expertise
- 3 Time Table
- 1 Service Tariffs

The way they are connected is shown below.

This is my data (I'm showing only what I'm using):

• Product

txtModel__c	Skill__c
Machine 1	A
Machine 2	B
Machine 3	C
Machine 4	D

• Expertise

SVMXC__Group_member__c	Region	SVMXC__Skill__c
FSE 1	Chile	A
FSE 1	Chile	C
FSE 2	LATAM North	B
FSE 2	LATAM North	C
FSE 2	LATAM North	D
FSE 3	Brazil	D

• 3 Time Table

• 1 Service Tariffs

For each section at the bottom of the page (Scope, Region, FSE, Type) I've entered 5 slicers that use data from the same column in the table.

- Scope = 3 Time Table[EQUIPMENT]

- FSE Region = 1 Service Tariffs[Region]

- Type = 1 Service Tariffs[Type]

• Final result

I had to disable the interaction among the rows, otherwise the slicers would filter each other and the cards would not work properly.

The measures I created to show Preparation on Site, Installation, Price and FSE Cost are:

_sum_preparation = 

VAR Selection = SELECTEDVALUE('3 Time Table'[EQUIPMENT])

RETURN
SWITCH(
    TRUE (),
    Selection <> "", SUM('3 Time Table'[Preparation on site (Week)])+0,
    "-"
)

_sum_installation = 

VAR Selection = SELECTEDVALUE('3 Time Table'[EQUIPMENT])

RETURN
SWITCH(
    TRUE (),
    Selection <> "", SUM('3 Time Table'[Installation (Week)]) + SUM('3 Time Table'[Installation (Sat)]) + SUM('3 Time Table'[Installation (Sun)]),
    "-"
)

_sum_price = 

VAR Selection = SELECTEDVALUE('3 Time Table'[EQUIPMENT])

RETURN
SWITCH(
    TRUE (),
    Selection <> "", SUM('3 Time Table'[Price]),
    "-"
)

_sum_FSE_cost = 

VAR Selection1 = SELECTEDVALUE('1 Service Tariffs'[Region])
VAR Selection2 = SELECTEDVALUE('1 Service Tariffs'[Type])

RETURN
SWITCH(
    TRUE (),
    Selection1 <>"" && Selection2 <> "", SUM('1 Service Tariffs'[Cost]),
    "-"
)

Now I'd like to calculate the final cost by adding up all the costs based on the slicer selection.

However, as they are using information from the same column, is this even possible to achieve?

I tried to create a virtual table in a measure but without success. I was trying to do something like:

[amitchandak]

@LPenatti , refer to the two methods where you can get two values on the same column

Compare two Brands: https://youtu.be/exN4nTewgbc

How Interactions Work- Split Page using interactions to compare: https://youtu.be/GIfRKzhMaR4

[LPenatti]

Hi @amitchandak, thanks for your help.

The first video worked for me, however I had to create one table for each slicer in Scope section (in my case, 5).

This is good when the number of slicers is low, but if want to add more slicers I'll have to add more tables, what is not good in my point of view, which causes performance issue in the report.

If I could have another way to do it, I'd try.