Weekly count for each category and then grouping

[MuthalibAbdul]

I am looking to find the average weekly count by a certain category and then group them depending on what the average is.

Example, if weekly average of America is 20, then group 0-20

if weekly average of europe is 40, then group 21-40 and so on. 

[v-ssriganesh · Accepted solution]

Hello @MuthalibAbdul,
Thank you for reaching out to the Microsoft Fabric Forum Community.

I have reproduced your scenario in Power BI and I was able to get the expected output as per your requirement calculating the average weekly count by each category and then grouping them based on that average. For your reference, I'm attaching the .pbix file.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[MuthalibAbdul]

If I want to add another column to this take is the Location column, can we have an average weekly location for each category?

[v-ssriganesh]

Hello @MuthalibAbdul,
Thanks for your follow up.

Yes, you can extend the solution to include a Location column. To calculate the average weekly count for each Category and Location, you'll need to adjust the logic slightly:

• Use the same approach we used before but group your calculations by both Category and Location along with the Week Start.
• Then, compute the average weekly count for each unique combination of Category and Location.
• You can apply the same grouping logic (e.g: 0–2, 3–4, etc.) based on that average.

Once this is done, you’ll be able to visualize and analyze the data at a more granular level per Category per Location.

[MuthalibAbdul]

Spot on!! Thank you!!

[Irwan]

hello @MuthalibAbdul 

perhaps create calculated column, then use if statement.

IF(
    [weekly average]<=20,
    "0-20",
IF(
    [weekly average]>20&&[weekly average]<=40,
    "21-40"
))

Hope this will help.

Thank you.