Forum Discussion

Mindslayer's avatar
Mindslayer
New Member
11 months ago
Solved

Need Roll up customization

I’m building a Call Plan dashboard in Power BI. I have a Roster table that contains Territory,Rep Name, Assignment Start Date and  Assignment End Date and few other fields. Territory Table - Territo...
  • v-ssriganesh's avatar
    v-ssriganesh
    11 months ago

    Hi Mindslayer,
    Thank you for reaching out to the Microsoft fabric community forum.     I reproduced your case in Power BI Desktop with sample data (Roster, Territory, Calendar, and CallFact tables). I applied the rollup rule where:

    • At Territory level, all reps are shown regardless of assignment date.
    • At Region/Area rollup level, only territories with reps assigned at least one month before the quarter start date are included.

    For example, with Q2 2024 (quarter start = 01-Apr, cutoff = 01-Mar):

    • T1 (01-Jan), T3 (10-Feb), and T4 (20-Jan) are included in rollups.
    • T2 (15-Mar) still appears at Territory level, but is excluded from Region/Area totals.

    Here’s the result I obtained:

    • Territory level → all territories show adherence individually.
    • Region R1 → T2 excluded, so rollup only reflects T1.
    • Region R2 → T3 and T4 both included.
    • Area A1 → combines R1 + R2 correctly, excluding T2.

    This matches your requirement where rollups are conditional while territory-level detail remains intact.

    For your reference, I’ve attached a .pbix file with the sample model, relationships, and DAX measures I used so you can test it directly with your own data.


    Best regards,
    Ganesh Singamshetty.

v-ssriganesh's avatar
v-ssriganesh
Icon for Community Support rankCommunity Support
11 months ago

Hi Mindslayer,
Thank you for reaching out to the Microsoft fabric community forum. I reproduced your case in Power BI Desktop with sample data (Roster, Territory, Calendar, and CallFact tables). I applied the rollup rule where:

  • At Territory level, all reps are shown regardless of assignment date.
  • At Region/Area rollup level, only territories with reps assigned at least one month before the quarter start date are included.

For example, with Q2 2024 (quarter start = 01-Apr, cutoff = 01-Mar):

  • T1 (01-Jan), T3 (10-Feb), and T4 (20-Jan) are included in rollups.
  • T2 (15-Mar) still appears at Territory level, but is excluded from Region/Area totals.

Here’s the result I obtained:

  • Territory level → all territories show adherence individually.
  • Region R1 → T2 excluded, so rollup only reflects T1.
  • Region R2 → T3 and T4 both included.
  • Area A1 → combines R1 + R2 correctly, excluding T2.

This matches your requirement where rollups are conditional while territory-level detail remains intact.

For your reference, I’ve attached a .pbix file with the sample model, relationships, and DAX measures I used so you can test it directly with your own data.


Best regards,
Ganesh Singamshetty.