topic Re: Dax Equations in DAX Commands and Tips

Dax Equations

hosea_chumba — Tue, 19 Jul 2022 07:37:31 GMT

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:

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
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".
Clustered column chart showing the trend of the [Weighted Implemetation] percentage for the 3 quarters in FY22 for the various [Office]s.
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

Re: Dax Equations

tamerj1 — Tue, 19 Jul 2022 18:31:03 GMT

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.] ) )

Re: Dax Equations

hosea_chumba — Tue, 19 Jul 2022 16:11:44 GMT

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.

Re: Dax Equations

tamerj1 — Tue, 19 Jul 2022 16:21:14 GMT

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.

Re: Dax Equations

hosea_chumba — Tue, 19 Jul 2022 16:28:31 GMT

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

Re: Dax Equations

tamerj1 — Tue, 19 Jul 2022 16:58:24 GMT

hosea_chumba

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

Re: Dax Equations

hosea_chumba — Tue, 19 Jul 2022 17:41:00 GMT

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

Re: Dax Equations

tamerj1 — Tue, 19 Jul 2022 17:45:51 GMT

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

Re: Dax Equations

tamerj1 — Tue, 19 Jul 2022 18:32:01 GMT

hosea_chumba
Please check the updates in the original reply