Forum Discussion
combine columns for pie chart
Hi guys,
I'll try and explain this as best as possible.
I am currently creating a training dashboard for moniotring the gym loading of athletes.
I have two tables. The first table has information about each exercise describing the 3 main muscle groups the exercise uses. The exercises are unique values in the table.
| Exercise | Primary Muscle Group | Secondary Muscle Group | Tertiary Muscle Group |
| Squat | Quads | Glutes | Lumbar |
| Bench Press | Pecs | Triceps | Shoulders |
| Press Up | Pecs | Triceps | Shoulders |
| Deadlift | Hamstrings | Lumbar | Glutes |
| Bench Row | Lats | Biceps | Shoulders |
The second table is a database listing what the athlete completes during the training session including the reps and the weight.
| Name | Exercise | Date | Reps | Sets | Weight | Total Weight Shifted |
| James | Squat | 24.08.2017 | 5 | 5 | 100 | 2500 |
| James | Bench Press | 24.08.2017 | 5 | 5 | 80 | 2000 |
| James | Press Up | 24.08.2017 | 10 | 4 | 10 | 400 |
| James | Deadlift | 24.08.2017 | 5 | 5 | 100 | 2500 |
| James | Bench Row | 24.08.2017 | 10 | 4 | 80 | 3200 |
| Jeff | Squat | 21.08.2017 | 5 | 5 | 75 | 1875 |
| Jeff | Bench Press | 21.08.2017 | 5 | 5 | 65 | 1625 |
| Jeff | Press Up | 21.08.2017 | 10 | 4 | 5 | 200 |
| Jeff | Deadlift | 21.08.2017 | 5 | 5 | 90 | 2250 |
| Jeff | Bench Row | 21.08.2017 | 10 | 4 | 65 | 2600 |
There is a relationship between the exercise columns in these tables.
Is there any was i can combine the muscle group columns in the first table so I can look at the total weight shifted across all muscle groups in a pie chart?
Many thanks,
James
Hi jgoodwin10,
You can refer to the below steps to get the desired results:
1. You need to unpivot the first table to get all muscles (primary, secondary and trinary) in a column
- Go to query editor mode and select the primary, secondary and trinary muscle column and click unpivot in transform tab
- Post this you will have 3 columns Exercise, Attributes (consist primary, secondary and trinary in rows) and values (Consist muscle in rows)
- Close and apply the changes
2. Now you have to create a DIM table which will help you create the relation between new unpivoted table
- you can use the distinct function for that
New table = DISTINCT(Exercise)
3. Create relation ship between all three tables using Exercise column
- Going forward using Excersing column from Dim table and use Value column (from unpivoted column) in pie chart
Hope this helps :)
-Sumit
3 Replies
- sumit4732
Advocate II
Hi jgoodwin10,
You can refer to the below steps to get the desired results:
1. You need to unpivot the first table to get all muscles (primary, secondary and trinary) in a column
- Go to query editor mode and select the primary, secondary and trinary muscle column and click unpivot in transform tab
- Post this you will have 3 columns Exercise, Attributes (consist primary, secondary and trinary in rows) and values (Consist muscle in rows)
- Close and apply the changes
2. Now you have to create a DIM table which will help you create the relation between new unpivoted table
- you can use the distinct function for that
New table = DISTINCT(Exercise)
3. Create relation ship between all three tables using Exercise column
- Going forward using Excersing column from Dim table and use Value column (from unpivoted column) in pie chart
Hope this helps :)
-Sumit
KasunpathiranaAdvocate IV
You can create the cross filteration as shown in the first picture and the result would be below when you create the Pie Chart. Hope this is the result you are expecting. I try this for the Primary Muscle Group but you can try for others too.
Hope this helps :smileyvery-happy:
Worked like a charm!
Thank you very much!
