- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Compare Row Value to Overall Average Respecting Slicer Filters
Hi there,
i have a table in the following format:
ID | Dim1 | Dim2 | Conversions | Overall Avg |
abc123 | type1 | blue | 3 | 3.67 |
abc456 | type2 | red | 5 | 3.67 |
cd4987 | type2 | green | 3 | 3.67 |
I would like to compare the individual row's conversion values to the overall average of conversions. BUT this calculation should respect filters set by slicers. So, for example, there might be a slicer on the page with a dropdown for dim1 and another one with a dropdown for dim2. If the user selects one of these dropdown values, the overall average should be recalculated.
What I want as the expected outcome is a measure that changes whenever one of the slicers is changed.
So far I've only managed to create a measure that gives me the overall average IGNORING the slicers, so for example in the table above the overall average would be 3.67. If the slicer for Dim1 is set to "type2", I want the measure for the overall average to recalculate and output 4.
Any combination of CALCULATE, ALLEXCEPT, ALL, FILTER... and so on that I've tried so far either only returned 3.67 all of the time, ignoring the slicers or just returned the average of the individual rows.
Any suggestions? Thanks in advance!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Respect filter
calculate(Average(Table[Conversions]) , allselected(Table))
do not respect filter
calculate(Average(Table[Conversions]) , all(Table))
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Respect filter
calculate(Average(Table[Conversions]) , allselected(Table))
do not respect filter
calculate(Average(Table[Conversions]) , all(Table))
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hey @amitchandak many thanks for your answer. this does in fact seem to work. no idea what I did wrong earlier.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
02-27-2024 06:49 PM | |||
07-21-2024 07:29 AM | |||
01-29-2024 03:28 PM | |||
02-27-2023 10:16 PM | |||
Anonymous
| 07-11-2024 02:37 AM |
User | Count |
---|---|
121 | |
79 | |
47 | |
45 | |
36 |
User | Count |
---|---|
179 | |
89 | |
69 | |
47 | |
47 |