Forum Discussion

addaline's avatar
addaline
Icon for Helper I rankHelper 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.

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

Not sure I fully understand your issue but you could try a pattern like this to get your desired result.  I can't tell if the P.R. and Days Worked columns are columns or measures so here are both version.

 

If columns

CompanyAverage = SUMX(VALUES(Table[CrewLeaderName]), CALCULATE(DIVIDE(SUM(Table[P.R.]), SUM(Table[Days Worked]))))

If measures 

CompanyAverage = SUMX(VALUES(Table[CrewLeaderName]), DIVIDE([P.R.], [Days Worked]))

 

You can wrap the whole thing in CALCULATE( ... , ... <> 0, ...<>0) if needed.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat