Forum Discussion

LPenatti's avatar
LPenatti
Helper II
4 years ago

Sum result using multiple slicers from same column

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__cSkill__c
Machine 1A
Machine 2B
Machine 3C
Machine 4D

 

  • Expertise
SVMXC__Group_member__cRegionSVMXC__Skill__c
FSE 1ChileA
FSE 1ChileC
FSE 2LATAM NorthB
FSE 2LATAM NorthC
FSE 2LATAM NorthD
FSE 3BrazilD

 

  • 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:

Need Help

2 Replies

    • LPenatti's avatar
      LPenatti
      Helper II
      4 years ago

      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.