Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
ewuchatka
Helper II
Helper II

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

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Zubair_Muhammad
Community Champion
Community Champion

@ewuchatka

 

Try this pattern

 

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

Regards
Zubair

Please try my custom visuals

@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

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

 

Try with this

 

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

Regards
Zubair

Please try my custom visuals

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

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

 @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

 

Hi,

 

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello @Ashish_Mathur

 

Thank you for you help! Is this what you need?

 

https://app.powerbi.com/groups/me/reports/2ecf1648-c49e-45d9-8506-bcea705d08b8?ctid=2fc13e34-f03f-49...

 

I also insert elow of a screenshot in excel where you can see - when I filter on "Agra" manufacturer in excel I need just "1" in WoD but in Power BI there is "2". Formula used in excel is =COUNTIF(B5:B8,"<1.00")

 

I'd like the calculation to work  whenever I filter manufacturer/account/packtype etc like in excel

 

pbi

 .

Thanks

Hi,

 

No.  I need to download your PBI file.  Upload it to Google Drive/One Drive and share the download link.  Also, i do not see any image in your previous message.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
tex628
Community Champion
Community Champion

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


Connect on LinkedIn

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.