Forum Discussion

alks_skla_f's avatar
alks_skla_f
Helper II
9 months ago
Solved

Count Average based on values from table visual

Hello, 
I have a table visual, which is filtered by using different options. The first filter is set on the right bar, where I filter "Status" and "New Value". The next filter is set in "Columns" section, where I filter First Closed Date by using Earliest keyword (case can be opened several times, I need to know only first closed date).

I have a measure "First Resolution Date Business Hours". My task is to count average for this measure.
To count average I am using this measure: 

measure_AverageFirstResolutionDateHours = 
    DIVIDE(
        -- returns sum of values in measure FirstResolutionHours
        SUMX(
            cps_sfdc_cases_history,
            [measure_FirstResolutionHours_Optimized]
        ),
        COUNTROWS(
            FILTER(
                cps_sfdc_cases_history,
                NOT(ISBLANK([measure_FirstResolutionHours_Optimized]))
            )
        ),
        0 -- Default value if the denominator is 0
    )

After I decided to filter table in order to have a small sample which helps me check if average is correct. I am getting incorrect average. The answer is 2.06, but should be 2.12:

The question is how I can count average of measure First Resolution Date Business Hours based on table visual, not table itself?

  • alks_skla_f , a new measure on top of your measure 

    Sumx(summarize (cps_sfdc_cases_history, cps_sfdc_cases_history[Case Numer], cps_sfdc_cases_history[CraetedDate], cps_sfdc_cases_history[End Date]) , [measure_AverageFirstResolutionDateHours]) 

    And try. In visual calculation you can try collapse all 

5 Replies

  • alks_skla_f , a new measure on top of your measure 

    Sumx(summarize (cps_sfdc_cases_history, cps_sfdc_cases_history[Case Numer], cps_sfdc_cases_history[CraetedDate], cps_sfdc_cases_history[End Date]) , [measure_AverageFirstResolutionDateHours]) 

    And try. In visual calculation you can try collapse all 

    • alks_skla_f's avatar
      alks_skla_f
      Helper II

      Unfortunately, it doesn't work. I accepted this as a solution by mistake

      • amitchandak's avatar
        amitchandak
        Super User

        alks_skla_f , Try Averagex 

         

        Averagex (summarize (cps_sfdc_cases_history, cps_sfdc_cases_history[Case Numer], cps_sfdc_cases_history[CraetedDate], cps_sfdc_cases_history[End Date]) , [measure_AverageFirstResolutionDateHours])

  • v-venuppu's avatar
    v-venuppu
    Community Support

    Hi alks_skla_f ,

    Thank you for reaching out to Microsoft Fabric Community.

    Thank you amitchandak for the prompt response.

    I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

    Thank you.

     

  • v-venuppu's avatar
    v-venuppu
    Community Support

    Hi alks_skla_f ,

    May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

    Thank you.