Forum Discussion
Helper IISolution for COUNTIFS
Please give this a try:
Column D =
CALCULATE (
COUNTROWS ( 'TableName' ),
ALLEXCEPT (
'TableName',
'TableName'[Team Name],
'TableName'[Week #],
'TableName'[Year]
)
)Hi ShrikantKhanna,
You can use EARLIER function when you create calculated column. I used FILTER(Table5,Table5[Team Name]=EARLIER(Table5[Team Name])), which mean it will return the subtable with same Team name. Filter is compared to the criteria. Then I add multiple filters, so the formula will count [Team Name] where satisfy Filter1, Filter2, Filter3 and so on.Result = CALCULATE(COUNTA(Table5[Team Name]),FILTER(Table5,Table5[Team Name]=EARLIER(Table5[Team Name])),FILTER(Table5,Table5[Week#]=EARLIER(Table5[Week#])),FILTER(Table5,Table5[Year]=EARLIER(Table5[Year])))
Please review expected result in the following screenshot.If you have other issues, please feel free to ask.
Best Regards,
Angelia
Helper IIHi Matt,
My main objective is to apply target for each team per week as it changes every week. I am able to use LOOKUPVALUE to call the taget in the main table, however, target is appearing for each row the team/Week#/Year combination appears. Like, ABC1, week 15, Year 2016-17 appears twice - the target appears twice. So, I am thinking to break down target by using this COUNTIFS to get weighted target per row and them SUM it for the Team Name, Region Name (for respective team names) and then overall, all in the main table.
I have tried to house targets in a separate table with one to many relationship, however, one of the slicer in the report (created from the main table) for "this Week", "Last Week" is not calling in the correct values. So, I thought of weightage calculation.
Please let me know your thoughts.
Thanks.
Microsoft EmployeeHi ShrikantKhanna,
You can use EARLIER function when you create calculated column. I used FILTER(Table5,Table5[Team Name]=EARLIER(Table5[Team Name])), which mean it will return the subtable with same Team name. Filter is compared to the criteria. Then I add multiple filters, so the formula will count [Team Name] where satisfy Filter1, Filter2, Filter3 and so on.
Result = CALCULATE(COUNTA(Table5[Team Name]),FILTER(Table5,Table5[Team Name]=EARLIER(Table5[Team Name])),FILTER(Table5,Table5[Week#]=EARLIER(Table5[Week#])),FILTER(Table5,Table5[Year]=EARLIER(Table5[Year])))
Please review expected result in the following screenshot.
If you have other issues, please feel free to ask.
Best Regards,
Angelia
- ShrikantKhanna
- v-huizhn-msft
Hi ShrikantKhanna，
Yes, "Earlier" still works eventhrough the data is not sorted.
Best Regards,
Angelia
I am not able to get your solution to work. I get this error: EARLIER/EARLIEST refers to an earlier row context which doesn't exist.
Result = CALCULATE(COUNTA(Tabel1[Business case]),FILTER(Tabel1, Tabel1[Business case]=EARLIER(Tabel1[Business case])), FILTER(Tabel1, Tabel1[Business case]=EARLIER(Tabel1[Business case])))
- ShrikantKhanna