Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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] ) )
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.
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] ) )
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!
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
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)
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!
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.
I had a quick look at your file. It's got some swag I must say . 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.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |