Forum Discussion
Super UserSegment respondents into buckets
Hi,
Given this simple table, i'd like to segment the respondent into 2 buckets - below average and above average. Please note that since the visual should respond to slicers, I cannot use a calculated column formula - therefore, please share a measure based solution. Also, rating (second column of base data sheet) is a measure. Thank you.
I am attaching an MS Excel file.
Base data
| Student | Rating | ||||||
| A | 4.2 | ||||||
| W | 3.5 | ||||||
| D | 4.9 | ||||||
| E | 4.1 | ||||||
| R | 3.2 | ||||||
| T | 3.5 | ||||||
| Y | 3.9
The expected result is this table visual
|
Hi Ashish_Mathur,
Here's a PBIX file with both PowerQuery and DAX solutions.
https://drive.google.com/file/d/1Gr1eZgToxX3TGSYsGaFXBZMeYC2HV4tD/view?usp=sharing
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.
If you need the total to appear in the table as well, try this:
Count_ = VAR _Avg = AVERAGEX ( ALLSELECTED ( 'Table'[Student] ), CALCULATE ( SUM ( 'Table'[Rating] ) ) ) VAR _Tbl = FILTER ( SUMMARIZECOLUMNS ( 'Table'[Student], "@particulars", IF ( CALCULATE ( SUM ( 'Table'[Rating] ) ) < _Avg, "Above average", "Below Average" ) ), [@particulars] IN VALUES ( Particulars[Particulars] ) ) RETURN COUNTROWS ( _Tbl )
4 Replies
- tayloramy
Hi Ashish_Mathur,
Here's a PBIX file with both PowerQuery and DAX solutions.
https://drive.google.com/file/d/1Gr1eZgToxX3TGSYsGaFXBZMeYC2HV4tD/view?usp=sharing
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.
- Ashish_Mathur
Thank you.
- danextian
If you need the total to appear in the table as well, try this:
Count_ = VAR _Avg = AVERAGEX ( ALLSELECTED ( 'Table'[Student] ), CALCULATE ( SUM ( 'Table'[Rating] ) ) ) VAR _Tbl = FILTER ( SUMMARIZECOLUMNS ( 'Table'[Student], "@particulars", IF ( CALCULATE ( SUM ( 'Table'[Rating] ) ) < _Avg, "Above average", "Below Average" ) ), [@particulars] IN VALUES ( Particulars[Particulars] ) ) RETURN COUNTROWS ( _Tbl )- Ashish_Mathur
Thank you. That worked.
