Trend visuals

[hosea_chumba]

Hello fellow community,

Kindly help to solve the below problem;

Consider the tables below, they are implementation tracking reports for 3 quarters in FY 22.

I would like to achieve the following:

1. Create a measure called "Weighted Implementation" in percentage in each quarter i.e. considering the following:
   • Each issue with "Closed" [Implementation Status] = 1 (i.e. the weight = Number of closed Issues * 1), "Partially Implemented" [Implementation Status] =0.5 (i.e. the weight =Number of Partially Implemented Issues * 0.5), "Open" [Implementation Status] = 0 (i.e. the weight = Number of Open Issues * 0)
   • To compute the Weighted Implemetation Percentage, divide:  Numerator= (Number of closed Issues *1 + No of Partially Implemented Issues * 0.5), Denominator = Total number of Issues i.e. count of the Unique references without any weighting, e.g. Total number of issues in FY22 Q1 =4, FY22Q3=6
2. A line and clustered column chart showing the [Weighted Implemetation] percentage as the secondary Y axis line chart for FY22Q3 and the immediate previous 2 quarters, X axis indicating the [Office] and Y axis indicating Number of "Open Issues".
3. Clustered column chart showing the trend of the [Weighted Implemetation] percentage for the 3 quarters in FY22 for the various [Office]s.
4. Note the below facts:
   • The [Implementation Status] changes over time (from Quarter to quarter) as reflected in the tables below for some of  the issues being tracked.
   • New Issues are added over time (Ideally the old issues can be dropped when they are too old depending on policy, in this example lets assume the issues are yet to be dropped)
   • @SpartaBI 

FY22Q1 Report
Unique Issue Tracking Ref.	Quarter the Issue was raised	Implementation Status	Office
123	FY21Q1	Open	Armenia
124	FY21Q3	Closed	Sri Lanka
125	FY22Q1	Partially Implemented	Senegal
126	FY22Q2	Open	Ecuardo
FY22Q2 Report
Unique Issue Tracking Ref.	Quarter the Issue was raised	Implementation Status	Office
123	FY21Q1	Open	Armenia
124	FY21Q3	Closed	Sri Lanka
125	FY22Q1	Partially Implemented	Senegal
126	FY22Q2	Open	Ecuardo
127	FY22Q3	Open	Armenia
FY22Q3 Report
Unique Issue Tracking Ref.	Quarter the Issue was raised	Implementation Status	Office
123	FY21Q1	Closed	Armenia
124	FY21Q3	Closed	Sri Lanka
125	FY22Q1	Closed	Senegal
126	FY22Q2	Open	Ecuardo
127	FY22Q3	Open	Armenia
128	FY22Q4	Partially Implemeted	Ecuardo

[lbendlin]

There is no need to have three separate tables

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRcos0Mgw0BDL8C1LzgJRjUW5qXmYiRMYIKBOrA1JqAlMK0uOck1+cmgJkBBdlKvgk5mVjKDdFCOgoBSQWlWQm5uRUKnjmFuSkAs0vgehOzUtNT8xB12sGEzBCuMo1uTQxJb8IXSkRHjAizQNGFHjAiHgPwJSawwSMCXoAxa9wD6ApNibNt8bYfItQjuo9Y+K9Z0y892BKLWACJngCHNOaWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique Issue Tracking Ref." = _t, #"Quarter the Issue was raised" = _t, #"Implementation Status" = _t, Office = _t, Quarter = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique Issue Tracking Ref.", Int64.Type}, {"Quarter the Issue was raised", type text}, {"Implementation Status", type text}, {"Office", type text}, {"Quarter", type text}})
in
    #"Changed Type"

Please indicate expected outcome based on this source data.

[hosea_chumba]

not working