cancel
Showing results for
Did you mean:
Regular Visitor

## Count if with existing measure

Hello

I've had almost a year-long break from Power BI  and I've tried to look for the answer on the forum but without any luck. I would appreciate your help.

I am trying to recreate COUNT IF from Excel.

Step 1:

I created a measure of average unit price (AUP): =Value Sales/Units Sold

Step 2:

Now I am trying to create a COUNT IF to count a number of weeks in a year where AUP was over 1.00.

Raw data is reported on weekly basis.

I'd like to create a table where I will have count of number of weeks where AUP was over 1.00 for:

2017

2018

2019

In Excel it is easy =COUNTIF(B76:BA76,"<1.00")

B76-BA76 is AUP by week for 2017

I'd be grateful for your help, thanks very much

12 REPLIES 12
Super User

## Re: Count if with existing measure

@ewuchatka

Try this pattern

```Measure =
COUNTX ( ALLSELECTED ( TableName[Week_Column] ), [AUP Measure] )```
Super User

## Re: Count if with existing measure

Hi,

Try this measure

=COUNTROWS(FILTER(SUMMARIZE(Calendar[Week Number],[Week Number],"ABCD",[AUP]),[ABCD]>1))

My solution is based on the following assumptions:

1. In the base data table there is a date column
2. There is a Calendar Table and there is a relatiosnhip from the Date column of the Base data table to the Date column of the Calendar Table
3. In the Calendar Table, there will be a week number column computed with this calculated column formula =WEEKNUM(Calendar[Date]).  There will also be a Year column in the Calendar Table with this calculated column formula =YEAR(Calendar[Date])
4. In the visual, you will drag the Year from the Calendar Table

Hope this helps.

Regular Visitor

## Re: Count if with existing measure

Thank you that's a great step towards what I need, it helps me calculate a total number of Weeks.

How can I amend the formula to get to number of weeks where AUP is below 1.10  i.e.  [AUP]<=1.10?

Thanks

Ewa

Senior Member

## Re: Count if with existing measure

Try creating a measure like this:

Measure = Calculate(Countrows([table]);[AUP]>1)

Assuming you have one row per week, place this in a table with a row for each year you should get the amount of weeks > 1.

/ Johannes

Super User

## Re: Count if with existing measure

@ewuchatka

Try with this

```Measure =
COUNTX (
FILTER ( ALLSELECTED ( TableName[Week_Column] ), [AUP Measure] < 1.1 ),
1
)```
Community Support Team

## Re: Count if with existing measure

Hi @ewuchatka

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Count if with existing measure

Hi guys

Thanks very much

I'm almost there.... I think the formula is counting rows and in the data there is more than one row per date as there are different products for each date. So what I need is for the formula to calculate AUP for each date and then count weeks with AUP <1.

Thanks

Super User

## Re: Count if with existing measure

Hi,

Have you tried my formula?  It will count the Week Numbers.

Regular Visitor

## Re: Count if with existing measure

Thanks

I'm not sure what I am doing wrong I created a calendar table with three columns (it is the first calendar table I ever created!?):

Date = CALENDAR (DATE(2000,1,1), DATE(2025,12,31))

Week Number = WEEKNUM('Date'[Date])

Year = YEAR('Date'[Date])

Then not sure but this formula doesn't work...

=COUNTROWS(FILTER(SUMMARIZE(Calendar[Week Number],[Week Number],"ABCD",[AUP]),[ABCD]>1))

Thanks

Ewa