Count if with existing measure

[ewuchatka]

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

[Ashish_Mathur]

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.

[Zubair_Muhammad]

@ewuchatka

Try this pattern

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

[ewuchatka]

@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

[v-piga-msft]

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

[Zubair_Muhammad]

@ewuchatka

Try with this

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

[ewuchatka]

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

[Ashish_Mathur]

Hi,

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

[ewuchatka]

 @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

[Ashish_Mathur]

Hi,

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

[ewuchatka]

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

 .

Thanks

[Ashish_Mathur]

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.

[tex628]

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