Forum Discussion

VijayAntonyM's avatar
VijayAntonyM
Icon for Helper I rankHelper I
1 year ago
Solved

Need help on DAX measures

Hi Team,

 

Good day,

Need help on DAX measure on below,

Would like to calculate the % of Percentage value based on Experience center, month-year,

 

 

Need to bring the On-Time % from the total count, 

Ex: Data and Analytics, Dec-2024 total=225

      On-Time: 214, Delayed:11
      Need percentage value on based total and ontime

 

Kindly help

 

regards

M. Vijay Antony

Tips and Tricks
  • danextian's avatar
    danextian
    1 year ago

    Both solutions should work as expected. Using the sample PBIX file from Ritaf1983 and adding the formula I initially provided, it is evident that regardless of whether the resolution flag is "on-time" or "delayed," the result remains consistent for each month-year and experience center. The key difference between the approaches is that hers uses three measures, while mine relies on just one.

     

     

     

     

  • danextian's avatar
    danextian
    1 year ago

    Can you please be more specific of what you really need? We've already provided you the formulas as well as screenshots of the report. Or do you want the same value for all months? If that is the case, please use this formula. Otherwise, please show us your expected result and not jus the data

    Pct On-Time = 
// Calculate the total number of On-Time resolutions
VAR _OnTime =
    CALCULATE (
        SUM ( 'table'[count of number] ),
        FILTER (
            ALL ( 'Table'[Resolution Flag], 'Table'[Month-Year] ),
            'table'[resolution flag] = "On-Time"
        )
    ) // Calculate the total number of Delayed resolutions
VAR _Delayed =
    CALCULATE (
        SUM ( 'table'[count of number] ),
        FILTER (
            ALL ( 'Table'[Resolution Flag], 'Table'[Month-Year] ),
            'table'[resolution flag] = "Delayed"
        )
    )
RETURN
    // Calculate the percentage of On-Time resolutions
    DIVIDE (
        _OnTime,
        _OnTime + _Delayed
    )

6 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    hi VijayAntonyM 

     

    Assuming Count of Number is a column

    Pct On-Time =
// Calculate the total number of On-Time resolutions
VAR _OnTime =
    CALCULATE (
        SUM ( 'table'[count of number] ),
        'table'[resolution flag] = "On-Time"
    ) // Calculate the total number of Delayed resolutions
VAR _Delayed =
    CALCULATE (
        SUM ( 'table'[count of number] ),
        'table'[resolution flag] = "Delayed"
    )
RETURN
    // Calculate the percentage of On-Time resolutions
    DIVIDE (
        _OnTime,
        _OnTime + _Delayed
    )

     

    If Count of Number isn't a column but count of table rows, use COUNTROWS ( 'table' ) instead.

    • VijayAntonyM's avatar
      VijayAntonyM
      Icon for Helper I rankHelper I
      1 year ago

      Team,

      Please understand , above 2 solutions are not working,

       

      Please see the example in simple way,

      Need to find the Ontime % based on Experience center, Month year , Need to count the Number column and find the On-Time %

      this value should be displayed in separte column and should be repeated in all rows.

      it should also work in even when we inlcude slicers.

       

      Regards

      M. Vijay Antony

       

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        1 year ago

        Both solutions should work as expected. Using the sample PBIX file from Ritaf1983 and adding the formula I initially provided, it is evident that regardless of whether the resolution flag is "on-time" or "delayed," the result remains consistent for each month-year and experience center. The key difference between the approaches is that hers uses three measures, while mine relies on just one.

         

         

         

         

  • Ritaf1983's avatar
    Ritaf1983
    Icon for Super User rankSuper User
    1 year ago

    Hi VijayAntonyM 
    You can use these 3 measures :

    Total_ count = CALCULATE(sum('Table'[Count of Number]),all('Table'[Resolution Flag]))
    On_time = CALCULATE(sum('Table'[Count of Number]),'Table'[Resolution Flag]= "On-Time")
    %on time = DIVIDE([On_time],[Total_ count])

    The pbix is attached

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.