Forum Discussion

addaline's avatar
addaline
Helper I
6 years ago
Solved

Company Average from filtered data

I have dug around the forums and have a general idea of what I should be doing to accomplish this, but I'm missing something, and hoping the hive mind can help.   I am trying to show the company av...
Need Help
  • addaline's avatar
    addaline
    6 years ago

    mahoneypat , v-eachen-msft : Thank you for your replies.

     

    Unfortunately neither result matched the Avg PR from the table, but in the course of working through your suggestions I think I found a result that works, using 3 Measures:

     

    PRTotal = SUM(PerformanceRatio[PerformanceRatio])

     

    DaysWorkedCount = COUNTA(PerformanceRatio[ReportedDate])

     

    CompanyAverage = 
    CALCULATE(
    [PRTotal]/[DaysWorkedCount],
    ALLSELECTED(PerformanceRatio))

     

    This matches the Avg PR displayed in the table, and adjusts along with the table based on the selected dates, people, etc. which it what I needed for the card.

     

    Thank you.

v-eachen-msft's avatar
v-eachen-msft
Community Support
6 years ago

Hi addaline ,

 

Do you mean to get a measure which is like "Avg PR"?

You could use ALLEXCEPT() to get result of each name:

CompanyAveragePR =
CALCULATE (
    DIVIDE (
        SUMX (
            FILTER ( PerformanceRatio, PerformanceRatio[PerformanceRatio] <> 0 ),
            PerformanceRatio[PerformanceRatio]
        ),
        COUNTX (
            FILTER ( PerformanceRatio, PerformanceRatio[Total] <> 0 ),
            PerformanceRatio[ReportedDate]
        ),
        0
    ),
    ALLEXCEPT ( PerformanceRatio, PerformanceRatio[CrewLeaderName] ),
    PerformanceRatio[Total] <> 0,
    PerformanceRatio[PerformanceRatio] <> 0
)

 

  • addaline's avatar
    addaline
    Helper I
    6 years ago

    mahoneypat , v-eachen-msft : Thank you for your replies.

     

    Unfortunately neither result matched the Avg PR from the table, but in the course of working through your suggestions I think I found a result that works, using 3 Measures:

     

    PRTotal = SUM(PerformanceRatio[PerformanceRatio])

     

    DaysWorkedCount = COUNTA(PerformanceRatio[ReportedDate])

     

    CompanyAverage = 
    CALCULATE(
    [PRTotal]/[DaysWorkedCount],
    ALLSELECTED(PerformanceRatio))

     

    This matches the Avg PR displayed in the table, and adjusts along with the table based on the selected dates, people, etc. which it what I needed for the card.

     

    Thank you.

    • v-eachen-msft's avatar
      v-eachen-msft
      Community Support
      6 years ago

      Hi addaline ,

       

      I am glad that you could find the solution. You could accept your own reply to close the topic.