Forum Discussion
Helper IComparison of data using multiple slicer
I've poured through so many forums and youtube videos on comparison using one or more slicer, none of them seem to work for me. I try those same example into my data and could not achieve what I want. Perhaps the great minds of the community can assist me. The objective:
I have a data table name workforce with columns company/agencies, race, year, value
| Company_or_agency | Race | Year | Value |
| A | Total Males | 2020 | 12 |
| A | Total Males % | 2020 | 10 |
| A | Total Females | 2020 | 2 |
| A | Total Females % | 2020 | 80.33 |
| A | Hispanic or Latino Male | 2020 | 1 |
| A | Hispanic or Latino Male % | 2020 | 8.33 |
| A | Hispanic or Latino Female | 2020 | 0 |
| A | Hispanic or Latino Female % | 2020 | 0 |
| A | White Male | 2020 | 9 |
| A | White Male % | 2020 | 75 |
| A | White Female | 2020 | 1 |
| A | White Female % | 2020 | 8.33 |
| A | Black or African American Male | 2020 | 0 |
| A | Black or African American Male % | 2020 | 0 |
| A | Black or African American Female | 2020 | 1 |
| A | Black or African American Female % | 2020 | 8.33 |
| A | Asian Male | 2020 | 0 |
| A | Asian Male % | 2020 | 0 |
| A | Asian Female | 2020 | 0 |
| A | Asian Female % | 2020 | 0 |
| A | Total Males | 2019 | 12 |
| A | Total Males % | 2019 | 80 |
| A | Total Females | 2019 | 3 |
| A | Total Females % | 2019 | 20 |
| A | Hispanic or Latino Male | 2019 | 1 |
| A | Hispanic or Latino Male % | 2019 | 6.67 |
| A | Hispanic or Latino Female | 2019 | 1 |
| A | Hispanic or Latino Female % | 2019 | 6.67 |
| A | White Male | 2019 | 11 |
| A | White Male % | 2019 | 73.33 |
| A | White Female | 2019 | 0 |
| A | White Female % | 2019 | 0 |
| A | Black or African American Male | 2019 | 0 |
| A | Black or African American Male % | 2019 | 0 |
| A | Black or African American Female | 2019 | 2 |
| A | Black or African American Female % | 2019 | 13.33 |
| A | Asian Male | 2019 | 0 |
| A | Asian Male % | 2019 | 0 |
| A | Asian Female | 2019 | 0 |
| A | Asian Female % | 2019 | 0 |
Then I created one table DimCompanyAgency that list all possible companies with a relationship to workforce.company_or_agency.
Then I created another create DimCompanyComparator with same data as DimCompanyAgency with an inactive relationship
Here is the interface management want. I even try to convince them to consolidate the drop down to one, but they want 3. First drop down is primary company, second drop is for comparing against company 2, and third drop down against company 3.
So What I want to do is have those 3 slicer filter the workforce table to only have records that pertain to the 3 slicers and straight display of the data using table.
Is it possible to program the 3 seperate slicer to make the workforce table contain only rows that have companies from slicer 1,2,3? or may be i'm approaching the idea the wrong way!!!
Thank you community if you can help me solve this problem.
Hi oluong ,
How about something like this?
Measure = VAR SelectedCompany2_ = SELECTEDVALUE ( DimCompanyAgency[Company_or_agency] ) VAR SelectedCompany3_ = SELECTEDVALUE ( DimCompanyComparator[Company_or_agency] ) VAR MainComapny_ = SUM ( workforce[Value] ) VAR Company2_ = CALCULATE ( SUM ( workforce[Value] ), workforce[Company_or_agency] = SelectedCompany2_ ) VAR Company3_ = CALCULATE ( SUM ( workforce[Value] ), workforce[Company_or_agency] = SelectedCompany3_ ) RETURN SWITCH ( SELECTEDVALUE ( Company[Company] ), "Main Company", MainComapny_, "Company 2", Company2_, "Company 3", Company3_ )For more details, check the attachment.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
- Icey
Community Support
Hi oluong ,
How about something like this?
Measure = VAR SelectedCompany2_ = SELECTEDVALUE ( DimCompanyAgency[Company_or_agency] ) VAR SelectedCompany3_ = SELECTEDVALUE ( DimCompanyComparator[Company_or_agency] ) VAR MainComapny_ = SUM ( workforce[Value] ) VAR Company2_ = CALCULATE ( SUM ( workforce[Value] ), workforce[Company_or_agency] = SelectedCompany2_ ) VAR Company3_ = CALCULATE ( SUM ( workforce[Value] ), workforce[Company_or_agency] = SelectedCompany3_ ) RETURN SWITCH ( SELECTEDVALUE ( Company[Company] ), "Main Company", MainComapny_, "Company 2", Company2_, "Company 3", Company3_ )For more details, check the attachment.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.