Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EikeMaas
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

@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] )
)

 

 

 

 

View solution in original post

13 REPLIES 13
AlB
Super User
Super User

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.

 

@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] )
)

 

 

 

 

EikeMaas
Frequent Visitor

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!


@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

 

@EikeMaas

 

I've updated the file to include the clusters

EikeMaas
Frequent Visitor

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

@EikeMaas

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

EikeMaas
Frequent Visitor

I definitely could - is there a way to upload the files directly to this forum? Or do I need to host somewhere and link?

By the way, I'm going to accept your earlier post as a solution since I'm steering this topic a little deeper & off course at this point. Thank you again for being patient and keeping in touch!

@EikeMaas

No worries. glad it helped.

Yeah you need to have the file somewhere else and post the URL here. I tend to use tinyupload.com,  which doesn't require sign-in, but there are many other options.  

@EikeMaas

 

I had a quick look at your file. It's got some swag I must say Smiley Happy.  Good job!

I'm not sure there's a simple way to reduce the number of measures. I'll try to take a more detailed look when I get some time.  

EikeMaas
Frequent Visitor

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

 

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.    

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.