Forum Discussion
Newb Question on table ranging
Hi
I'm completely new to Power BI and have been playing around with it, but having trouble grouping specific data.
I like to create a table where I can group specific age range. For example, I would like to see under Sony what is a range of player's age between 10-18, 19-27, 28-36, 37-42 and 43+. Is there a formula to do this under Measure?
Below is a table I'm using as a test.
Thank you in advance for anyone who can help!
| Console | Counties | Player's Age |
| Sony | Bedfordshire | 12 |
| Sony | Berkshire | 13 |
| Sony | Bristol | 25 |
| Sony | Buckinghamshire | 36 |
| Sony | Cambridgeshire | 44 |
| Sony | Cheshire | 50 |
| Sony | City of London | 22 |
| Sony | Cornwall | 19 |
| Sony | County Durham | 18 |
| Sony | Cumbria | 16 |
| Xbox | Bedfordshire | 26 |
| Xbox | Berkshire | 22 |
| Xbox | Bristol | 19 |
| Xbox | Buckinghamshire | 13 |
| Xbox | Cambridgeshire | 32 |
| Xbox | Cheshire | 34 |
| Xbox | City of London | 40 |
| Xbox | Cornwall | 38 |
| Xbox | County Durham | 37 |
| Xbox | Cumbria | 51 |
| Switch | Bedfordshire | 24 |
| Switch | Berkshire | 27 |
| Switch | Bristol | 31 |
| Switch | Buckinghamshire | 16 |
| Switch | Cambridgeshire | 17 |
| Switch | Cheshire | 36 |
| Switch | City of London | 35 |
| Switch | Cornwall | 35 |
| Switch | County Durham | 25 |
| Switch | Cumbria | 24 |
Hi naiconn1981,
We can create the measures as below.
Sony 10-18 = CALCULATE(COUNTROWS(Table1),FILTER(ALL(Table1),Table1[Console]="Sony" && Table1[Player's Age]>=10 && Table1[Player's Age]<=18))
Sony 19-27 = CALCULATE(COUNTROWS(Table1),FILTER(ALL(Table1),Table1[Console]="Sony" && Table1[Player's Age]>=19 && Table1[Player's Age]<=27))
Sony 28-36 = CALCULATE(COUNTROWS(Table1),FILTER(ALL(Table1),Table1[Console]="Sony" && Table1[Player's Age]>=28 && Table1[Player's Age]<=36))
Sony 37-42 = CALCULATE(COUNTROWS(Table1),FILTER(ALL(Table1),Table1[Console]="Sony" && Table1[Player's Age]>=37 && Table1[Player's Age]<=42))
Sony 43+ = CALCULATE(COUNTROWS(Table1),FILTER(ALL(Table1),Table1[Console]="Sony" && Table1[Player's Age]>=43))
For more details, please check the pbix as attached.
https://www.dropbox.com/s/hd56qny81bc8232/Newb%20Question%20on%20table%20rangin.pbix?dl=0
Regards,
Frank
3 Replies
Hi naiconn1981,
We can create the measures as below.
Sony 10-18 = CALCULATE(COUNTROWS(Table1),FILTER(ALL(Table1),Table1[Console]="Sony" && Table1[Player's Age]>=10 && Table1[Player's Age]<=18))
Sony 19-27 = CALCULATE(COUNTROWS(Table1),FILTER(ALL(Table1),Table1[Console]="Sony" && Table1[Player's Age]>=19 && Table1[Player's Age]<=27))
Sony 28-36 = CALCULATE(COUNTROWS(Table1),FILTER(ALL(Table1),Table1[Console]="Sony" && Table1[Player's Age]>=28 && Table1[Player's Age]<=36))
Sony 37-42 = CALCULATE(COUNTROWS(Table1),FILTER(ALL(Table1),Table1[Console]="Sony" && Table1[Player's Age]>=37 && Table1[Player's Age]<=42))
Sony 43+ = CALCULATE(COUNTROWS(Table1),FILTER(ALL(Table1),Table1[Console]="Sony" && Table1[Player's Age]>=43))
For more details, please check the pbix as attached.
https://www.dropbox.com/s/hd56qny81bc8232/Newb%20Question%20on%20table%20rangin.pbix?dl=0
Regards,
Frank
v-frfei-msft thanks Frank. I will try this. I was hoping to consign them under a bar chart but guess I can do something similar using the formulas.
Hi naiconn1981,
Has your problem been solved, if any other question, feel free to let me know.
Regards,
Frank
