Clustered colum chart issue

[Anonymous]

Hi All,

Need your help in the below requirement.

Datasources - 2 excel files (Demand & Capacity files)

Demand File Fields(Year,demand Hours & Roles)
Capacity File field (Year,Capacity Hours & Roles)
difference_Measure = Demand hours - capacity

when i plot a clustered colum chart with Demand Hrs,Capacity hrs and difference_Measure from both sheet and Year(x axis) all works fine but when i add filter based on role it Sums up either Demand/Capacity and shows wrong values.

Role       Capacity_hrs Year
BA             10              Jan-20
SAP             20             Feb-20
MICRO             10     Apr-20
ORACLE             40    May-20
PEOPLE SOFT     35     Jun-20
RAMCO             44 Jul-20
QLIKVIEW     51 Aug-20
MYSUCESS     58 Sep-20
PM             65 Oct-20
SCM             72 Nov-20
RACO             79 Dec-20
BA             10 Apr-20
SAP             20 May-20
MICRO             10 Apr-20
ORACLE             40 May-20
PEOPLE SOFT     35 Jun-20

Role Demand hrs Year
BA       20                      Jan-20
SAP       30                     Feb-20
MICRO       35             Apr-20
ORACLE       25         May-20
PEOPLE SOFT   15 Jun-20
RAMCO       10            Jul-20
QLIKVIEW      12      Aug-20
MYSUCESS       9     Sep-20
PM        6                      Oct-20
SCM        3 Nov-20
RACO       45 Dec-20
BA       20 Jan-20
SAP       30 Feb-20
MICRO       35 Oct-20
ORACLE       25 Nov-20
PEOPLE SOFT   15 Dec-20

Can you please help me in this.

Regards,
Sam

[v-diye-msft · Accepted solution]

HI @Anonymous 

Not quite sure your requirement, kindly check if you'd like to get below results:

Pbix attached.

[Anonymous]

Hi Dina,

When we filter based on any role am getting wrong out put.

but the relations in your model sounds would solve my problem.

 Table 2 consists of Roles from only one sheet how about evening adding roles from other sheet ?

Can you help me with that

Regards,

Sam