Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Hi,
Try this measure
=COUNTROWS(FILTER(SUMMARIZE(Calendar[Week Number],[Week Number],"ABCD",[AUP]),[ABCD]>1))
My solution is based on the following assumptions:
Hope this helps.
Try this pattern
Measure = COUNTX ( ALLSELECTED ( TableName[Week_Column] ), [AUP 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
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
Try with this
Measure = COUNTX ( FILTER ( ALLSELECTED ( TableName[Week_Column] ), [AUP Measure] < 1.1 ), 1 )
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.
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.
Hello @Ashish_Mathur
Thank you for you help! Is this what you need?
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
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.
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
107 | |
105 | |
90 | |
61 |
User | Count |
---|---|
169 | |
138 | |
135 | |
102 | |
86 |