Forum Discussion

PadilhaBI's avatar
PadilhaBI
New Member
4 years ago

Aggregate data by month, count rows that meet condition and filter by not grouped by columns

I have a table with the following columns:

  • Date by day
  • Value sold
  • Goal value
  • Company

I want to aggregate the value sold and goal value by month, then check wether each month surpassed it's goal, and count how many did it.

 

I managed to get this done by creating a new table and grouping the date by month and counting how many months met the criteria. The problem is I still want to use the company column to filter, so I can see how many months met the criteria for each selected company or combination of companies.

 

Any ideas on how to do it are welcome!

Need Help

3 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    HI PadilhaBI ,

    Try to create the below column:

    Month = FORMAT('Table'[Date by day],"YYYY/MM")

    then get the sum value according month:

    SUMACCORDMONTH = CALCULATE(SUM('Table'[Value sold]),FILTER(ALL('Table'),'Table'[Month]=EARLIER('Table'[Month])&&'Table'[Company]=EARLIER('Table'[Company])))

     

    And then use the below to get the account :

    COUNTSATISFIED ACCORDING MON = CALCULATE(DISTINCTCOUNT('Table'[Company]),FILTER(ALL('Table'),'Table'[SUMACCORDMONTH]>='Table'[Goal value]&&'Table'[Month]=EARLIER('Table'[Month])))

    Output result:

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

    • PadilhaBI's avatar
      PadilhaBI
      New Member
      4 years ago

      Hey v-luwang-msft, thanks for answering!

       

      Your solution didn't actually work for my intended purpose. The resulting column: 

      COUNTSATISFIED ACCORDING MON

      shows me the count for how many companies met their goal each month. I wanted to check how many months met the aggregated goal, and be able to filter out companies to see the results.

       

      The solution you proposed is static, what I mean by that is that it's not affected by filters.

       

      Anyway I learned some new things from your post, thanks!

      • v-luwang-msft's avatar
        v-luwang-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi PadilhaBI ,

        Could you pls share a sample data,and expected output?

        Remember to remove confidential data.

         

         

         

        Best Regards

        Lucien