Forum Discussion

BieBel's avatar
BieBel
Icon for Helper I rankHelper I
11 months ago
Solved

Calculations with measures return odd results

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 !

 

 

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    11 months ago

    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.

10 Replies

  • BieBel's avatar
    BieBel
    Icon for Helper I rankHelper I
    10 months ago

    I made changes / corrections to my data model, which led to a solution

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    11 months ago

    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's avatar
      BieBel
      Icon for Helper I rankHelper I
      11 months ago

      Hello amitchandak,

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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    11 months ago

    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.


    If the above given solution doesn't meet your requirment, please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
    Please show the expected outcome based on the sample data you provided.
    Need help uploading data? How to provide sample data in the Power BI Forum - Microsoft Fabric Community
    Want faster answers? How to Get Your Question Answered Quickly - Microsoft Fabric Community

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


    Thank you .

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    11 months ago

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    11 months ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    11 months ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 months ago

    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.

  • Rufyda's avatar
    Rufyda
    Icon for Super User rankSuper User
    10 months ago

    Hi BieBel 
    Use these two measures instead of the old formulas:

     

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

     

    Then 
    Productivity =
    DIVIDE(
    [Closed_Tickets_per_Month],
    [TotalDays]
    )

    Did it work? ✔ Give a Kudo • Mark as Solution – help others too!