cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ewuchatka Regular Visitor
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
Super User

Re: Count if with existing measure

@ewuchatka

 

Try this pattern

 

Measure =
COUNTX ( ALLSELECTED ( TableName[Week_Column] ), [AUP Measure] )
Super User
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.

ewuchatka Regular Visitor
Regular Visitor

Re: Count if with existing measure

@Zubair_Muhammad

 

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

tex628 Senior Member
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
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
Community Support Team

Re: Count if with existing measure

Hi @ewuchatka

 

Have you solved your problem?

 

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.

 

If you need additional help, please feel free to ask.

 

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.
ewuchatka Regular Visitor
Regular Visitor

Re: Count if with existing measure

Hi guys

 

Thanks very much Smiley Happy

 

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
Super User

Re: Count if with existing measure

Hi,

 

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

ewuchatka Regular Visitor
Regular Visitor

Re: Count if with existing measure

 @Ashish_Mathur

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