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

Compare two sets of data using two slicers in one visualization.

I hope I'm using the correct terminology - but I'm very new to this software, so please teach me the right way!

What I'd like to do is use two slicers to define two sets of data from one table, and then compare the respective averages of each set to the other.

For example:

 

PROJECT NAME | SQFT | PRICE
Project 1 | 500 | $400
Project 2 | 1,500 | $1,100
Project 3 | 300 | $300
Project 4 | 900 | $700

If Slicer 1 on a page is set to "Project 1 & Project 2", and Slicer 2 is set to "Project 3 & Project 4", I want a Clustered Column Chart that displays the averages for each column of Project 1 & Project 2 vs the averages for each column of Project 3 & Project 4.

So I'd like to see:

"Avg (Project 1 & Project 2)" | 1,000 | $750     vs.    "Avg (Project 3 & Project 4)" | 600 | $500

Where the two clusters are SQFT and PRICE, and each cluster has a column for "Avg (Project 1 & Project 2)" and "Avg (Project 3 & Project 4)" respectively.

I hope that makes sense... I'm still learning all of the terminology, let alone the tricks to getting the data to perform the way I want it to. It really made searching for an answer difficult, so I'd appreciate any help you could lend. 

Thanks,
Eike

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Compare two sets of data using two slicers in one visualization.

@EikeMaas

 

You could also use another approach. It's similar to the one presented above but you'd need to create two inactive relationships:

a) Between Slicer1[PROJECT NAME] and Table1[PROJECT NAME]

b) Between Slicer2[PROJECT NAME 2] and Table1[PROJECT NAME]

 

We would then use the following measures that make use of USERELATIONSHIP  instead of TREATAS. The rest is the same as in the previous approach

 

Group1_Sqft = 
CALCULATE (
    AVERAGE ( Table1[SQFT] );
    USERELATIONSHIP ( Slicer1[PROJECT NAME]; Table1[PROJECT NAME] )
) 

 

Group1_Price =
CALCULATE (
    AVERAGE ( Table1[PRICE] );
    USERELATIONSHIP ( Slicer1[PROJECT NAME]; Table1[PROJECT NAME] )
)

 

Group2_Sqft =
CALCULATE (
    AVERAGE ( Table1[SQFT] );
    USERELATIONSHIP ( Slicer2[PROJECT NAME 2]; Table1[PROJECT NAME] )
)
Group2_Price = 
CALCULATE (
    AVERAGE ( Table1[PRICE] );
    USERELATIONSHIP ( Slicer2[PROJECT NAME 2]; Table1[PROJECT NAME] )
)

 

 

 

 

13 REPLIES 13
Super User
Super User

Re: Compare two sets of data using two slicers in one visualization.

Hi @EikeMaas

I'm not completely sure it is what you had in mind but let's try this:

1. Create two additional tables that will be used for slicing.  These tables have only one column, the 'PROJECT NAME' column from your data table. 

   a) Table Slicer1 with one column named 'PROJECT NAME' 

   b) Table Slicer2 with one column named 'PROJECT NAME 2' (a different name for the column just to tell the slicers apart easily)

 

2. Create the following measures for the price and sqft for each of the two groups of projects (Table1 is your initial data table):

 

V2_Group1_Sqft = 
CALCULATE (
    AVERAGE ( Table1[SQFT] );
    TREATAS ( VALUES ( Slicer1[PROJECT NAME] ); Table1[PROJECT NAME] )
)
V2_Group1_Price = 
CALCULATE (
    AVERAGE ( Table1[PRICE] );
    TREATAS ( VALUES ( Slicer1[PROJECT NAME] ); Table1[PROJECT NAME] )
)
V2_Group2_Sqft = 
CALCULATE (
    AVERAGE ( Table1[SQFT] );
    TREATAS ( VALUES ( Slicer2[PROJECT NAME 2] ); Table1[PROJECT NAME] )
)
V2_Group2_Price = 
CALCULATE (
    AVERAGE ( Table1[PRICE] );
    TREATAS ( VALUES ( Slicer2[PROJECT NAME 2] ); Table1[PROJECT NAME] )
)

3. Place two slicers on your report, one with Slicer1[PROJECT NAME] and the other with Slicer2[PROJECT NAME 2]. We'll use them to select the first and second group of projects for the calculations

 

4. Place a clustered column chart on your report with all four measures in values

 

You can now play with the slicers to select what projects will be included in the price and sqft calculations and the results will be shown in your chart.

 

Super User
Super User

Re: Compare two sets of data using two slicers in one visualization.

@EikeMaas

 

You could also use another approach. It's similar to the one presented above but you'd need to create two inactive relationships:

a) Between Slicer1[PROJECT NAME] and Table1[PROJECT NAME]

b) Between Slicer2[PROJECT NAME 2] and Table1[PROJECT NAME]

 

We would then use the following measures that make use of USERELATIONSHIP  instead of TREATAS. The rest is the same as in the previous approach

 

Group1_Sqft = 
CALCULATE (
    AVERAGE ( Table1[SQFT] );
    USERELATIONSHIP ( Slicer1[PROJECT NAME]; Table1[PROJECT NAME] )
) 

 

Group1_Price =
CALCULATE (
    AVERAGE ( Table1[PRICE] );
    USERELATIONSHIP ( Slicer1[PROJECT NAME]; Table1[PROJECT NAME] )
)

 

Group2_Sqft =
CALCULATE (
    AVERAGE ( Table1[SQFT] );
    USERELATIONSHIP ( Slicer2[PROJECT NAME 2]; Table1[PROJECT NAME] )
)
Group2_Price = 
CALCULATE (
    AVERAGE ( Table1[PRICE] );
    USERELATIONSHIP ( Slicer2[PROJECT NAME 2]; Table1[PROJECT NAME] )
)

 

 

 

 

Super User
Super User

Re: Compare two sets of data using two slicers in one visualization.

@EikeMaas

 

Using physical relationships is generally recommended whenever possible so the USERELATIONSHIP approach should theoretically be more efficient.

In this file you can see a quick test to illustrate  what's been presented so far.    

EikeMaas Frequent Visitor
Frequent Visitor

Re: Compare two sets of data using two slicers in one visualization.

Hi AIB, thank you for your thorough responses.

I have created the two Tables named "Slicer1" and "Slicer 2", but I get this error when creating the measures (I've tried both creating the measures in the Slicer 1 and Slicer 2 tables, as well as in Table 1 (my original query).

"USERELATIONSHIP function can only use the two columns references participating in a relationship."

What does that mean? 

EikeMaas Frequent Visitor
Frequent Visitor

Re: Compare two sets of data using two slicers in one visualization.

Opening your sample file, I was able to set up the relationships between the tables the same way to achieve your result - although I'm still not sure what the different types of relationships are and how they work, I'll do some more reading. 

This has gotten me started succesfully, but now I'd like to organize how the data is clustered - I want the two SQFT bars to be in one cluster, and the two PRICE bars to be in another cluster on the Clustered Column Graph. I hope that I'm getting my idea across. 

I think I'd do this by using the Axis field - however I don't know what column to put in Axis, or how to organize the Measures.

Thank you again for getting me this far and providing the example file!


Super User
Super User

Re: Compare two sets of data using two slicers in one visualization.

@EikeMaas

 

Using what we have from earlier, try this for the clustering:

 

1. Create a table Clusters with one column called Cluster and as row values the names of your clusters, for instance "Sqft"and "Price" 

2. Set Clusters[Cluster] in the axis of the clustered column chart.

3. Create two new measures:

 

Group1 =
SWITCH (
    SELECTEDVALUE ( 'Clusters'[Cluster] );
    "Sqft"; [V2_Group1_Sqft];
    "Price"; [V2_Group1_Price]
)
Group2 =
SWITCH (
    SELECTEDVALUE ( 'Clusters'[Cluster] );
    "Sqft"; [V2_Group2_Sqft];
    "Price"; [V2_Group2_Price]
)

4. Place both measures in values of the clustered chart

 

Super User
Super User

Re: Compare two sets of data using two slicers in one visualization.

@EikeMaas

 

I've updated the file to include the clusters

EikeMaas Frequent Visitor
Frequent Visitor

Re: Compare two sets of data using two slicers in one visualization.

Thank you again! In the image below I've uploaded photos (just realized how to do it) of the actual metrics I'm trying to create. 

I'm basically comparing the area of various rooms within an apartment against the rooms in comparable apartments. In the images below, Group 1 is comparable projects, while Group 2 is the active project(s). 

In the second image, I want to do a side-by-side comparison for each room type - So each slice of the pie in the top image, gets a side-by-side bar graph with some stats called out in the bottom image. This is creating a LOT of measures, though! Especially to create the cards with Min, Max, etc called out below the bar graphs - each of those is a measure returning a single value. You can see, if I have 8 different room types to compare, and 7 measures for each room type - that's 56 measures! Is that normal? It seems like there would be a more efficient way to process the data.

In the third image I've shown how my query table is formatted - in addition to the columns you see there w/ general project information that I use for filtering (like Penthouse?), the different room types also each have their own column, which you begin to see to the right of the table (ex: Total Bed SF)

image.pngimage.pngimage.png

Super User
Super User

Re: Compare two sets of data using two slicers in one visualization.

@EikeMaas

I cannot see the pics well. Would you be able to share the pbix?