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.

MarkLaf's avatar
MarkLaf
Icon for Super User rankSuper User
11 months ago

Not getting into the specifics of your calc requirements or model, the typical way to control aggregation by level is with ISINSCOPE.

 

Simple example:

 

Table

Area Region Territory
Area A Region A Territory A
Area B Region B Territory B
Area C Region C Territory C

 

Measure = 
IF(
    ISINSCOPE( 'Table'[Territory] ),
    "<territory measure>",
    "<upper level measure>"
)