Dax Equations

[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

[hosea_chumba]

Thanks so much @tamerj1 , did you use a formula to add the  [Report Quarter] column for the respective quarters taking into consideration that it is large data. 

[tamerj1]

@hosea_chumba 
 Actually yes. I assumed that this is the case of your model. Otherwise why would you keep them in separate tables and add a new table every new quarter? I know saving one column is a good idea but sometimes you need to do the math. 

[hosea_chumba]

You assumed right @tamerj1 , how did you add the the [Report Quarter]column for each quarter?

[tamerj1]

@hosea_chumba 

Actully I did that manually in power powery for each table separately. You can see the code in the sample file. 

[hosea_chumba]

The formula is not showing, any other way of handling it?

[tamerj1]

@hosea_chumba 
Please check the updates in the original reply

[tamerj1]

Ok I'll update the original solution with some screenshots. Just give me some time to open my computer. 

[tamerj1 · Accepted solution]

Hi @hosea_chumba 
Please refer to the sample file with the proposed solution https://www.dropbox.com/t/0yKTjmbzDXqrfUv6

Weighted Implementation = 
DIVIDE (
    SUMX (
        SUMMARIZE ( 
            'Compiled Report', 
            'Compiled Report'[Unique Issue Tracking Ref.], 
            'Compiled Report'[Implementation Status] 
        ),
        CALCULATE ( 
            SELECTEDVALUE ( Weightages[Weight] ), 
            CROSSFILTER ( 'Compiled Report'[Implementation Status], Weightages[Status], Both ) 
        )
    ),
    DISTINCTCOUNT ( 'Compiled Report'[Unique Issue Tracking Ref.] )
)