Calculations with measures return odd results

[BieBel]

I work within an organisation which performs technical interventions and which is driven by a ticketing system.

Now I need to calculate PRODUCTIVITY: the number of closed tickets per workingday.

To do this I created a number of measures and when I verify each measure, I seem to get the right result but when I make the final calculation, I get numbers that are unexpected

I calculate the theoretical number of available days per month:              

Available_Workdays_per_month = ([Available_Technicians_per_month] * [DaysInMonth])

I calculate the days with absences (every day is listed, weekends and holiday included):              

Absences = CALCULATE(SUM(Absences[AbsenceDuration]))

So I get the number of netto available days:         

Netto_Workdays_per_month = [Available_Workdays_per_month] - [Absences]

I also calculate the number of closed tickets/completed interventions:               

Closed_Tickets_per_Month = COUNTROWS(Closed_Tickets_Filtered) from the filtered table

                          Closed_Tickets_Filtered =

COUNTROWS(

    SUMMARIZE('Closed_Tickets_Filtered',

   'Closed_Tickets_Filtered'[WR_ID],

   'Closed_Tickets_Filtered'[MONTHCOMPLETED],

   'Closed_Tickets_Filtered'[REGION]

   ))

And finally I devide the number of completed tickets by the number of netto available days and this is where I get the wrong results:              

Productivity = (DIVIDE ([Closed_Tickets_per_Month],[Netto_Workdays_per_month]))

Expected

Tables and Relationships

 

 So, somewhere along the way I did something wring but I cannot figure it out.

Can anyone see where I went wrong?

Thanks in advance !

[v-tsaipranay]

Hi @BieBel ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.

[v-tsaipranay]

Hi @BieBel ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

Thank you.

[Ashish_Mathur]

Hi,

Share a dummy dataset to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[v-tsaipranay]

Hi @BieBel ,

I wanted to follow up on our previous suggestions. We would like to hear back from you to ensure we can assist you further.

Thank you.

[v-tsaipranay]

Hi @BieBel,

Thanks for reaching out to the Microsoft fabric community forum. Thank you @amitchandak  for your helpful ressponse.

In the earlier formula, "_days" is not an actual column from your model it serves as a temporary column name (alias) within the SUMMARIZE function. This alias stores the [Netto_Workdays_per_month] result for each group before aggregation. You can rename it to something clearer, such as "NetDays".

Here’s the adjusted version:

Total days =
SUMX (
   SUMMARIZE (
      'Closed_Tickets_Filtered',
      'Closed_Tickets_Filtered'[WR_ID],
      'Closed_Tickets_Filtered'[MONTHCOMPLETED],
      'Calendar'[Month],
      "NetDays", [Netto_Workdays_per_month]
   ),
   [NetDays]
)

By doing this, [Netto_Workdays_per_month] is calculated at the correct month/region level first and then aggregated. This resolves the issue of unexpected productivity results.

Finally, your Productivity measure can be written as:

Productivity =
DIVIDE ( [Closed_Tickets_per_Month], [Total days] )

This ensures the numerator and denominator are aligned at the same level of detail, giving you the expected outcome.

Hope this helps. Please feel free to rech out for any further questions.

Thank you .

[BieBel]

@amitchandak 

[amitchandak]

@BieBel , This need correct for total =

Total days = Sumx(

    SUMMARIZE('Closed_Tickets_Filtered',

   'Closed_Tickets_Filtered'[WR_ID],

   'Closed_Tickets_Filtered'[MONTHCOMPLETED],

   'Calendar'[Month], "_days", [Netto_Workdays_per_month ]

   ), [_days])

Use the correct group by at the month level, employee level 

[BieBel]

Hello amitchandak,

I do not see what the "_days" is in your formula? Could you please elaborate on this?