Forum Discussion
Dynamic Grouping on Sum
I have data like
userid | profile percent | Date |
1 | 20 | jan 10 |
1 | 30 | jan 20 |
2 | 55 | jan 7 |
2 | 30 | jan 25 |
we have date selection and on the selected date we want to show data like
0-50 percent | no of users
51-75 percent | no of users
75 - 100 percent | no of users
profile percentage will be added till the selected date
Thanks is advance
Referred here from another thread, try this:
OK, based on the data in the other post, I created a Category table with:
Category
0-50 percent 51-75 percent 75-100 percent And a measure like this:
Measure = VAR __Date = MAX('Table'[date]) VAR __Category = MAX('Categories'[Category]) VAR __Low = SWITCH( __Category, "0-50 percent",0, "51-75 percent",.51, "75-100 percent",.75 ) VAR __High = SWITCH( __Category, "0-50 percent",.5, "51-75 percent",.74, "75-100 percent",1 ) VAR __tmpTable = SUMMARIZE('Table','Table'[userid],"__Percent",MAX('Table'[profilepercent])) RETURN COUNTROWS(FILTER(__tmpTable,[__Percent]>=__Low && [__Percent]<=__High))PBIX is attached.
7 Replies
Referred here from another thread, try this:
OK, based on the data in the other post, I created a Category table with:
Category
0-50 percent 51-75 percent 75-100 percent And a measure like this:
Measure = VAR __Date = MAX('Table'[date]) VAR __Category = MAX('Categories'[Category]) VAR __Low = SWITCH( __Category, "0-50 percent",0, "51-75 percent",.51, "75-100 percent",.75 ) VAR __High = SWITCH( __Category, "0-50 percent",.5, "51-75 percent",.74, "75-100 percent",1 ) VAR __tmpTable = SUMMARIZE('Table','Table'[userid],"__Percent",MAX('Table'[profilepercent])) RETURN COUNTROWS(FILTER(__tmpTable,[__Percent]>=__Low && [__Percent]<=__High))PBIX is attached.
