cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hosea_chumba
Frequent Visitor

Trend visuals

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 raisedImplementation StatusOffice
123FY21Q1OpenArmenia
124FY21Q3ClosedSri Lanka
125FY22Q1Partially ImplementedSenegal
126FY22Q2OpenEcuardo
    
    
FY22Q2 Report   
Unique Issue Tracking Ref.Quarter the Issue was raisedImplementation StatusOffice
123FY21Q1OpenArmenia
124FY21Q3ClosedSri Lanka
125FY22Q1Partially ImplementedSenegal
126FY22Q2OpenEcuardo
127FY22Q3OpenArmenia
    
    
FY22Q3 Report   
Unique Issue Tracking Ref.Quarter the Issue was raisedImplementation StatusOffice
123FY21Q1ClosedArmenia
124FY21Q3ClosedSri Lanka
125FY22Q1ClosedSenegal
126FY22Q2OpenEcuardo
127FY22Q3OpenArmenia
128FY22Q4Partially ImplemetedEcuardo
2 REPLIES 2
lbendlin
Super User
Super User

There is no need to have three separate tables

lbendlin_0-1658097215363.png

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.

not working

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors