Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Summing columns based on quarter value

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 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous,

    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.

3 Replies

  • Vijay_Chethan's avatar
    Vijay_Chethan
    Resolver III
    1 year ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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 ))

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous,

    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.