Forum Discussion
Grouping Values
Hi All,
I have a Number column from 1-20 but I am not able to group like
1-10
1-15
1-20
Please help.
Hi Kolumam,
Try the following steps
1. Import your data set
2. Create 2 more references of your data set
3. Now you will have 3 copies of your data.
4. Filter the data sets as follows
Filter your 1st data set such that you have data for those records where the number column will have values between 1 and 10,
2nd data set will have data for those records where the number column will have values between 1 and 15,
3rd data set will have data for those records where the number column will have values between 1 and 20,
5. Now create a static column.
In the 1st data set, this column should have 1-10 as the data
In the 2nd data set, this column should have 1-15 as the data
In the 3rd data set, this column should have 1-20 as the data
6. Now append (union) these 3 queries
7. After appending, you will have a single dataset will 3 sets of similar data
8. Now use distinct values of this new column in your slicer selection.
9. Your visual should have data from this new data set
10. Changing Selections in the slicer will not filter the data for the groups
But this has couple of limitations.
1. Atleast one group in the slicer should always be selected. If not selected, the all the 3 copies will be displayed, which is not right
2. This will result in multiple copies of the same data. But this will not affect performance as we are using reference
In case of queries, let me know. Will try to help
Hope this solves your requirement!!!
19 Replies
Create a grouping table. Make it contain at least 2 columns. First column is the number you will find in your data. Each other column will be the grouping you want to use. For example it might look like:
Headers: Value, GroupA, GroupB, GroupC
Row1: 1, "0-10", "0-15", "0-20"
...
...
Row16: 16, "11-20", "16-30", "0-20".Now join your existing data table to this reference table via the value field.
Also you can try a calculated column on this pattern
Group Column = SWITCH ( TRUE (), Table1[Numbers] <= 10, "1-10", Table1[Numbers] <= 20, "11-20", "Above 20" )Hi Zubair,
Group Name = SWITCH ( TRUE (), Savings[Year] <= 10, "10 Year", Savings[Year] <= 15, "15 year", Savings[Year] <= 20, "20 year")I was using the above formula but the issue is when the user clicks on "15 year", the graph just shows years from 11 to 15. See below pic when I click on 15 year. How do I make it to display from 1 to 15?
Hi Anonymous Zubair_Muhammad
How do I get rid of the 2nd row in all the groups? I don't need 11-20, 16-20
Kolumam My table was an example. I assume you know your groupings and would create approprately.
Thejashwar,
I haven't got a solution for this.
It's just upto 20. How do I have 3 filters as 1-10, 1-15 & 1-20 so that people can click on the 3 filters and see the data. Currently I am getting others. Grouping doesn't work
Hi Kolumam,
Try the following steps
1. Import your data set
2. Create 2 more references of your data set
3. Now you will have 3 copies of your data.
4. Filter the data sets as follows
Filter your 1st data set such that you have data for those records where the number column will have values between 1 and 10,
2nd data set will have data for those records where the number column will have values between 1 and 15,
3rd data set will have data for those records where the number column will have values between 1 and 20,
5. Now create a static column.
In the 1st data set, this column should have 1-10 as the data
In the 2nd data set, this column should have 1-15 as the data
In the 3rd data set, this column should have 1-20 as the data
6. Now append (union) these 3 queries
7. After appending, you will have a single dataset will 3 sets of similar data
8. Now use distinct values of this new column in your slicer selection.
9. Your visual should have data from this new data set
10. Changing Selections in the slicer will not filter the data for the groups
But this has couple of limitations.
1. Atleast one group in the slicer should always be selected. If not selected, the all the 3 copies will be displayed, which is not right
2. This will result in multiple copies of the same data. But this will not affect performance as we are using reference
In case of queries, let me know. Will try to help
Hope this solves your requirement!!!
