cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Walt
Frequent Visitor

Comparing 2 Nested Slicers in a Table

Is there an easy way to compare the 2 nested slicers?
Example1:
Select: Slicer1 - Parent2, Group1, Type3 and Type4
Select: Slicer2 – Parent1, Group2 (Selecting all types in Group2)
Slicer1 = 5
Slicer2 = 10
Measures:
DivideSlicers = Divide(Slicer2,Slicer1) this would equal 2

Example2:
Select: Slicer1 – Type3
Select: Slicer2 – Type7
Slicer1 = 3
Slicer2 = 4
Measure:
DivideSlicers = Divide(Slicer2,Slicer1) this would equal 1.33

Slicer1

  • Parent1
    • Group1
      • type1
      • type2
    • Group2
      • Type5
      • Type6
      • Type7
    • Parent2
      • Group1
        • Type1 - Value 6
        • Type3 – Value 3
        • Type4 – Value 2

 

Slicer2

  • Parent1
    • Group1
      • type1
      • type2
    • Group2
      • Type5 – Value 2
      • Type6 – Value 4
      • Type7 – Value 4
    • Parent2…

Any help would be greatly appreciated.

Thank you,

 

1 ACCEPTED SOLUTION
Walt
Frequent Visitor

7 REPLIES 7
Walt
Frequent Visitor

I can currently get single data types from each slicer, however i cant get multiple types from a single slicer. 

Hi, @Walt 

Are you currently using a hierarchy slicer? What do 'single data type' refer to?

Please share a sample .pbix for further research.

Best Regards,
Community Support Team _ Eason

Yes, they are Hierarchy Slicers.
Single Data types like in the examples, 1Type 2Type and so on.
Here is another Example:
Slicer 1 I select..
From in Parent 1
in Group 2
1Type it has a value of 10

Then Slicer 2 Select 2 Types
From Parent 2
in Group 2
3Type with a value of 20
and
4Type with a value of 30

I would like to be able to compare the 1 selected type in Slicer 1 to the 2 selected types in Slicer 2.
So I could say... AVG the Slicer 2 Values to 25 or I could Sum them to 50 then compare them to the Slicer 1 Value of 10. 
SUM(Slicer2) / SUM (Slicer1) or Average(Slicer2)/Average(Slicer1) or Average(Slicer2)/SUM(Slicer1)

Hi, @Walt 

Still just confused by part of your explanation, whether your slicer is multi-select or not, the final result should be aggregated.

Do aggregation operations like sum and average of fields in the table work in your case?

Measure2 = Average(Slicer2[Value])/SUM(Slicer1[Value])

 

Best Regards,
Community Support Team _ Eason

Looking at your Example.  I am trying to accomplish something different.  I have a it setup as 3 seperate Tables.  The Parent, Type, and Group are identical.  Should I be doing something else?

Slicer1(Created with Dax):
- Parent
- Type
- Group
Slicer2(Created with Dax):
- Parent
- Type
- Group

DataValues (Data Source):
- Parent
- Type
- Group
- Values

 

Walt
Frequent Visitor

I was able to find the solution here.  Thanks for you help

https://community.powerbi.com/t5/Desktop/Two-slicer-on-same-column/m-p/516318/thread-id/241351

 

v-easonf-msft
Community Support
Community Support

Hi, @Walt 

You need to copy a new table and filter the data separately by these two slicers to compare the results.

Result =
SUM ( 'Table1'[value] ) / SUM ( 'Table2'[value] )

Best Regards,
Community Support Team _ Eason

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors