combine columns for pie chart

[jgoodwin10]

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

[sumit4732 · Accepted solution]

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

[jgoodwin10]

Worked like a charm!

Thank you very much!

[Kasunpathirana]

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