Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a table with the following columns:
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!
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
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!
Hi @PadilhaBI ,
Could you pls share a sample data,and expected output?
Remember to remove confidential data.
Best Regards
Lucien
User | Count |
---|---|
86 | |
82 | |
68 | |
67 | |
55 |
User | Count |
---|---|
123 | |
100 | |
90 | |
83 | |
66 |