Company Average from filtered data

[addaline]

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

[v-eachen-msft]

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 · Accepted solution]

@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]

Hi @addaline ,

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

[mahoneypat]

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