cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ShrikantKhanna Regular Visitor
Regular Visitor

Solution for COUNTIFS

Hi,

I am trying to achieve a simple COUNTIFS solution in Power BI. I have read multiple messages but none of them are working out for me. I am trying to add a new column in my table and calculate the following:

 

COUNTIFS($A:$A,A3,$B:$B,B3,$C:$C,C3)

 

Col ACol BCol CCol D
Team NameWeek#YearCount IF
ABC1152016-172
ABC1152016-172
ABC2152016-173
ABC2152016-173
ABC2152016-173
ABC3152016-173
ABC3152016-173
ABC3152016-173
ABC4152016-173
ABC4152016-173
ABC4152016-173
ABC5152016-173
ABC5152016-173
ABC5152016-173
ABC6152016-173
ABC6152016-173
ABC6152016-173
ABC1162016-173
ABC1162016-173
ABC1162016-173
ABC2162016-173
ABC2162016-173
ABC2162016-173
ABC3162016-173
ABC3162016-173
ABC3162016-173
ABC4162016-173
ABC4162016-173
ABC4162016-173
ABC5162016-173
ABC5162016-173
ABC5162016-173
ABC6162016-173
ABC6162016-173
ABC6162016-173

 

I do not wish to create a new measure as I have to use this column to calculate another one. 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Amratya Regular Visitor
Regular Visitor

Re: Solution for COUNTIFS

Please give this a try:

Column D =
CALCULATE (
    COUNTROWS ( 'TableName' ),
    ALLEXCEPT (
        'TableName',
        'TableName'[Team Name],
        'TableName'[Week #],
        'TableName'[Year]
    )
)

v-huizhn-msft Super Contributor
Super Contributor

Re: Solution for COUNTIFS

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.

1.PNG

 

If you have other issues, please feel free to ask.

Best Regards,
Angelia

10 REPLIES 10
Super User
Super User

Re: Solution for COUNTIFS

What can't you use a measure to calculate another measure?  Why does it have to be a column?



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Amratya Regular Visitor
Regular Visitor

Re: Solution for COUNTIFS

Please give this a try:

Column D =
CALCULATE (
    COUNTROWS ( 'TableName' ),
    ALLEXCEPT (
        'TableName',
        'TableName'[Team Name],
        'TableName'[Week #],
        'TableName'[Year]
    )
)

ShrikantKhanna Regular Visitor
Regular Visitor

Re: Solution for COUNTIFS

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

v-huizhn-msft Super Contributor
Super Contributor

Re: Solution for COUNTIFS

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.

1.PNG

 

If you have other issues, please feel free to ask.

Best Regards,
Angelia

Super User
Super User

Re: Solution for COUNTIFS

@ShrikantKhanna your original approach is the correct one.  You should put the targets for each week in a second data table and join this table to common lookup tables.  

 

What is the issue with the slicer you mentioned?  Can you post a simple PBIX file (say dropbox link or something) with some sample data loaded and the tables all joined etc?  This makes it much easier to help you.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
ShrikantKhanna Regular Visitor
Regular Visitor

Re: Solution for COUNTIFS

@v-huizhn-msft

 

Will using "Earlier" in filter work if the data is not sorted? 

ShrikantKhanna Regular Visitor
Regular Visitor

Re: Solution for COUNTIFS

@MattAllington

I will create a sample and share with you in some time. There are 3-4 criterias that I have to include to try and replicate real scenario. 

In the mean time what I have done so far to fix my target issue is I have summarized my main table and dropped one criteria (out of 4) and called in target and sum of sales in it to create my presentation but I am really keen to create everything from my main table.

I will share with you as soon as I have create a sample for you to review.

 

Thanks!

 

and Thanks @Amratya @v-huizhn-msft 

Highlighted
v-huizhn-msft Super Contributor
Super Contributor

Re: Solution for COUNTIFS

Hi @ShrikantKhanna

Yes, "Earlier" still works eventhrough the data is not sorted.

Best Regards,
Angelia

CasperUni Visitor
Visitor

Re: Solution for COUNTIFS

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

 

SUMIF or COUNTIF.png