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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors