How to take total of one type of work per month

[vchauhan011]

Hello,

I have data set which contains months, Types of work and hours for each type of work per month. I want to show total hours per month in clustered chart A and in the clustered chart B I want to show hours for each work streem. I want to set it up in a way so that when I select any month in clustered chart A,  it shows total number of hours per each work streem for that month in clustered chart B and when nothing is selected it in clustered chart A, It shows total number of hours year to date per work type.    

[v-shex-msft]

HI @vchauhan011,

As d_gosbell said, you can consider doing 'unpivot columns' on your month field and write measure formula to achieve your requirement:

Unpivot columns (Power Query) 

Measure =
CALCULATE (
    SUM ( Table[Hour] ),
    ALLSELECTED ( Table ),
    VALUES ( Table[Work] ),
    VALUES ( Table[Month] )
)

Regards,

Xiaoxin Sheng

[vchauhan011]

Thank you very much for your suggetion! 

[d_gosbell · Accepted solution]

To do this you should structure your data so that you have 3 columns Month, WorkType and Amount. (you can do this with an "unpivot" transformation)

[vchauhan011]

Thank you very much for your help!