Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Count values for a column while filtering another column

Hi Power BI Comunity, please help

I need to count values for column "Items_Category" in Power BI, if my source only gives me 2 columns- Category & Items?

Items_Category is the output in 3rd column/ measure derived by adding items based on Category, Eg: Electronics = 50+125+200 = 375, but it has to show for all Electronics related rows.

 

Should i add column or use measure- please suggest what, why and how- it would be great.

 

CategoryItemsItems_Category
Electronics50375
Clothing75450
Miscellaneous100525
Electronics125375
Clothing150450
Miscellaneous175525
Electronics200375
Clothing225450
Miscellaneous250525
Need Help
  • Pragati11's avatar
    Pragati11
    6 years ago

    Hello @Gguliani ,

    Try creating a column like the following:

    CountCalc ? CALCULATE(SUM(tablename[Items]), FILTER(tablename, tablename[Category] ? EARLIER(tablename[Category] )))

    Thank you

    Pragati

6 Replies

  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    Hi Anonymous ,

     

    Your question is not very clear. Can you describe more on what's your current input is and what output is required?

     

    Thanks,

    Pragati

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Sorry, i realised that too and updated it, please check

  • Pragati11's avatar
    Pragati11
    Super User
    6 years ago

    Hello @Gguliani ,

    Try creating a column like the following:

    CountCalc ? CALCULATE(SUM(tablename[Items]), FILTER(tablename, tablename[Category] ? EARLIER(tablename[Category] )))

    Thank you

    Pragati

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Getting an error Pragati11 , field names and table names are different than dummy values asked in the query

       

       

      • Pragati11's avatar
        Pragati11
        Super User
        6 years ago

        Hi Anonymous ,

         

        You forgot to close a bracket after SUM function in your DAX. Check my dax expression again.

         

        Thanks,

        Pragati