Forum Discussion

ewuchatka's avatar
ewuchatka
Icon for Helper II rankHelper II
8 years ago

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

 

 

 

Need Help

12 Replies

    • ewuchatka's avatar
      ewuchatka
      Icon for Helper II rankHelper II
      8 years ago

      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

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        ewuchatka

         

        Try with this

         

        Measure =
COUNTX (
    FILTER ( ALLSELECTED ( TableName[Week_Column] ), [AUP Measure] < 1.1 ),
    1
)
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    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.