Multiple Column/Dimension Data Visualization

[mskusace]

I have a table that has multiple columns that I would like to visualize. Each row of data consists of a user, their associated department, division, and section, which is the drill down I use in the other areas of the dashboard. There are 13 columns that have "Yes" or "No" as the field values. Each of these 13 columns indicates whether or not that user needs certain training in one or more of these 13 areas. I would like to create a bar chart that has each of these 13 columns on the X-axis and the "Yes" count of each respective column on the Y-axis. I can use a Pivot Table and put the "Department" as the row and then each of the 13 columns as a column, to get a total count of "Yes" values. I then manually created a 2 column table with each of the 13 columns in one column and the associated count in the other column. 

Is there a way I can manipulate my data within the bar graph chart to create a "Yes" count of each of the 13 columns?

Raw Data

User

Department

Division

Section

Training1

Training2

Training3

Training4

Training5

Training6

Training7

Training8

Training9

Training10

User1

Department1

Division1

Section1

Yes

No

No

No

No

No

No

No

No

Yes

User2

Department1

Division2

Section2

No

No

No

No

No

No

No

Yes

No

No

User3

Department2

Division3

Section3

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Yes

User4

Department2

Division3

Section3

No

No

No

No

No

No

No

No

No

No

User5

Department3

Division4

Section4

Yes

No

No

Yes

No

No

No

Yes

No

No

Desired Results in Tabular Form

Sum of Training1	Sum of Training2	Sum of Training3	Sum of Training4	Sum of Training5	Sum of Training6	Sum of Training7	Sum of Training8	Sum of Training9	Sum of Training10
3	1	1	2	1	1	1	3	1	2

Then I want to create a bar graph with "Training1", "Training2"..."Training10" on the X-axis and the count of each respective training on the Y-axis.

Please let me know if I need to clarify further. Any assistance would be greatly appreciated!

[AngeloBaruffi]

You need to load this table using CrossTable:

After, you can use the training dimension with Count({<Value= {Yes}>}Value)

To help users find verified answers, please don't forget to mark a correct resolution or answer to your problem or question as correct.

[Vegar · Accepted solution]

I used this script.

YESMAP:
Mapping LOAD Bool, dual(Bool,Num)  inline [
Bool, Num
Yes, 1
No, 0];

MAP  [Training done] using 'YESMAP';
Crosstable ('Training type', 'Training done', 4)
LOAD * inline [
User,Department,Division,Section,Training1,Training2,Training3,Training4,Training5,Training6,Training7,Training8,Training9,Training10
User1,Department1,Division1,Section1,Yes,No,No,No,No,No,No,No,No,Yes
User2,Department1,Division2,Section2,No,No,No,No,No,No,No,Yes,No,No
User3,Department2,Division3,Section3,Yes,Yes,Yes,Yes,Yes,Yes,Yes,Yes,Yes,Yes
User4,Department2,Division3,Section3,No,No,No,No,No,No,No,No,No,No
User5,Department3,Division4,Section4,Yes,No,No,Yes,No,No,No,Yes,No,No
];

exit script

Then I could reate this app (sceenshot and attached app) Using the training type dimension and sum[(Training done]) as a measure

[mskusace]

I apologize for the delayed response. I was trying to play around with the command initially and got stuck. I finally revisited it and I was able to get it work perfectly! I am not sure how you got your columns to be the training type and a single row to be the measure, but I got it to work as the training types in one column and the count in the adjacent column. It really was as simple as using:

Crosstable ('Training type', 'Training done')
LOAD
[Training1],
[Training2],
[Training3],
[Training4],
[Training5],
[Training6],
[Training7],
[Training8],
[Training9],
[Training10]
FROM [Sheet1.xlsx]
(ooxml, embedded labels, table is Sheet1);

I will now try to incorporate the department, division, and section as you have in your example. This will do per my requirements for now!