Forum Discussion

horror's avatar
horror
Regular Visitor
8 years ago

Slicer on a Sum

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?

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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

    EmployeeTrainingDate
    GregTraining 19/19/2017
    BillTraining 19/19/2017
    JoeTraining 19/19/2017
    DavidTraining 19/19/2017
    JimboTraining 19/19/2017
    GregTraining 28/19/2017
    BillTraining 28/19/2017
    DavidTraining 28/19/2017
    GregTraining 37/19/2017
    BillTraining 37/19/2017
    JoeTraining 37/19/2017
    DavidTraining 37/19/2017

     

    Hours

    EmployeeWeekHours
    Greg9/17/201740
    Greg9/10/201740
    Greg9/3/201740
    Greg8/27/201740
    Greg8/20/201740
    Greg8/13/201740
    Greg8/8/201740
    Greg8/1/201740
    Joe9/17/201724
    Joe9/10/201724
    Joe9/3/201724
    Joe8/27/201724
    Joe8/20/201724
    Joe8/13/201724
    Joe8/8/201724
    Joe8/1/201724

     

    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])
		)
)
  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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