Summing columns based on quarter value

[wokka]

Hi

I have a simple table called   cal_table  with 3 columns across it -  year, date of activity,  quarter.

For example, for one row, you would have values    "2024", "01/07/2024", "1"  -  for an activity that occurred in 2024 on 01/07/2024 and in Q1.

I want to count the number of 1's , 2's, 3's and 4's ( i.e. the number of activities in each quarter ) so I can graph that value. I thought of a computed column but am not really sure best way to go about it.

Can someone help please?

Cheers 

[Anonymous · Accepted solution]

Hi @wokka,

Thanks for reaching out to the Microsoft fabric community forum.

If you want to count the number of activities in each quarter based on your "cal_table", you don’t need a computed column for that. The best way is to use a SQL "GROUP BY" query to summarize the data.

Assuming you already have a column named quarter in your table, here’s a query you can use:

SELECT
quarter,
COUNT(*) AS activity_count
FROM
cal_table
GROUP BY
quarter
ORDER BY
quarter;

This will return the number of activities for each quarter (1 to 4), which you can then use to create your graph. 

I would also take a moment to thank @Vijay_Chethan, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[wokka]

I think I have worked it out :

I created 4 new measures , one that counts the number of rows for each of the 4 quarter  column values of either 1,2,3 or 4 ( the 4 quarters )  :

_count_Q1 = COUNTROWS(FILTER('cal_table', 'cal_table'[quarter] = 1 ))

_count_Q2 = COUNTROWS(FILTER('cal_table', 'cal_table'[quarter] = 2 ))

_count_Q3 = COUNTROWS(FILTER('cal_table', 'cal_table'[quarter] = 3 ))

_count_Q4 = COUNTROWS(FILTER('cal_table', 'cal_table'[quarter] = 4 ))

[Vijay_Chethan]

Hello
you could change the quadrant to text type where now while using in visual , this can act as legend to get distribution of activities across quadrants

if this helps, pls mark as solution