Slicer on a Sum

[horror]

Hi,

I have a simple PowerBI that gives me a userlist with their summed of sales ($).

Underlying data is:

customerid, date, sum(sales)

from 1st january - 1 october

I would like to be able to set a slicer, that the viewer can filter for customers e.g. that spend > 200$.

thing is that the slicer applies to the underlying data, so it filters out single rows < 200$ and does not apply to the summed up values shown in the table.

Anybody can explain me how I can make a slicer that applies to the summed up values?

[v-jiascu-msft]

Hi @horror,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[Greg_Deckler]

What you are looking for is the disconnected table trick. I have an upcoming blog post on this in the Community Blog (under the "more" dropdown above). Basically, create a table that is unrelated to anything with your slicer values. Here is an abbreviated version of the article:

Given the set of these core tables:

Employees

Employee

Greg

Bill

Joe

David

Jimbo

Training

Employee	Training	Date
Greg	Training 1	9/19/2017
Bill	Training 1	9/19/2017
Joe	Training 1	9/19/2017
David	Training 1	9/19/2017
Jimbo	Training 1	9/19/2017
Greg	Training 2	8/19/2017
Bill	Training 2	8/19/2017
David	Training 2	8/19/2017
Greg	Training 3	7/19/2017
Bill	Training 3	7/19/2017
Joe	Training 3	7/19/2017
David	Training 3	7/19/2017

Hours

Employee	Week	Hours
Greg	9/17/2017	40
Greg	9/10/2017	40
Greg	9/3/2017	40
Greg	8/27/2017	40
Greg	8/20/2017	40
Greg	8/13/2017	40
Greg	8/8/2017	40
Greg	8/1/2017	40
Joe	9/17/2017	24
Joe	9/10/2017	24
Joe	9/3/2017	24
Joe	8/27/2017	24
Joe	8/20/2017	24
Joe	8/13/2017	24
Joe	8/8/2017	24
Joe	8/1/2017	24

The Employees table is simply a unique list of Employee ID's. The Training table has a row for every training event that an employee attended. Finally, the Hours table lists the week ending date when an employee worked. Employees are expected to attend training events when they are working and are not expected to attend training events when they are not working. The Hours table has been abbreviated.

The relationships between the tables are as follows. All relationships are between the Employee columns of the tables.

Employees 1<>* Hours

Employees 1>* Training

Measures:

Attendance = IF(ISBLANK(MAX([Date])),"Not Attended","Attended")

Attended = IF(ISBLANK(MAX([Date])),BLANK(),"Attended")
NotAttended = IF(ISBLANK(MAX([Date])),"Not Attended",BLANK())

Measures to Show = 
IF(
        // This first IF condition forces the measure to evaluate in the context of the visual
	HASONEVALUE(Employees[Employee]),
                // This next condition avoids getting the error that the visual cannot be displayed
		IF(HASONEVALUE(Attendance[Attendance]),
                        // This switch statement uses the values from our Attendance slicer to determine which measure to display
			SWITCH(
          			VALUES(Attendance[Attendance]),
          			"Attended",[Attended],
          			"Not Attended",[NotAttended]
	 			),
                        // If the Attendance slicer has not been selected, just display the date of the training
			MAX([Date])
		)
)