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 average of a value on a card. I get the value in a table, and the table shows the average correctly when filtering by date, etc.

 

I need to get that average on a card.


Exmaple of table with average:

 

 

 

I created a measure as follows:

 

---

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

---


But, as noted in the in the above table, it's coming up with the wrong result, apparently taking everything into account.

 

Where am I going wrong?

 

Thanks,

 

Dion

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.

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft 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

     

  • 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.