Need Roll up customization

[Mindslayer]

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 - Territory, Region , Area and their names

At the territory level, I want to show adherence for all territories, regardless of when the rep was assigned.

However, at the region (or higher) rollup level, there is a specific requirement:
Only those territories should be included in the rollup where reps were assigned at least one month prior to the start of the quarter being viewed. Territories with late rep assignments should be excluded from the regional calculation.

For example:

• If the quarter start date is April 1, then only territories with reps assigned on or before March 1 should contribute to the region’s adherence rollup.

• A territory with a rep assigned on March 15 would still show up individually at the territory level, but would be excluded when rolling up to the region.

Has anyone implemented a similar requirement? What’s the best DAX or model design approach to enforce this conditional inclusion/exclusion at different levels of aggregation? Since roll ups are automatically done in Power BI I am confused how to use them.
Lets say I have this DAX calculation what do I do? Or is it something applied at model level

Adherence_Overall =
VAR __CurrDate = MAX ( DIM_Calendar[dt] )
VAR __QtrStart = MIN ( DIM_Calendar[frst_dd_qtr] )
VAR __QtrEnd   = MAX ( DIM_Calendar[last_dd_qtr] )

-- Total calls completed up to current date (QTD cumulative)
VAR __CallsCompletedQTD =
    CALCULATE (
        [Total_Target_Calls],
        DIM_Calendar[dt] >= __QtrStart,
        DIM_Calendar[dt] <= __CurrDate
    )

-- Business days elapsed in the quarter up to current date
VAR __BusinessDaysElapsed =
    CALCULATE (
        COUNTROWS ( DIM_Calendar ),
        DIM_Calendar[wk_dd] = TRUE,
        DIM_Calendar[dt] >= __QtrStart,
        DIM_Calendar[dt] <= __CurrDate
    )

-- Total business days in the quarter
VAR __BusinessDaysTotal =
    CALCULATE (
        COUNTROWS ( DIM_Calendar ),
        DIM_Calendar[wk_dd] = TRUE,
        DIM_Calendar[dt] >= __QtrStart,
        DIM_Calendar[dt] <= __QtrEnd
    )

-- Prorated goal = fraction of quarter elapsed × quarter call goal
VAR __ScaledGoal =
    DIVIDE ( __BusinessDaysElapsed, __BusinessDaysTotal, 0 )
        * [Call Goal]

RETURN
    DIVIDE ( __CallsCompletedQTD, __ScaledGoal, 0 )

[FBergamaschi]

Hi @Mindslayer,

to understand the DAX results we need the model

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Alternatively, you can share your .pbix via some cloud service and paste the link here. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here

[MarkLaf]

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

[v-ssriganesh · Accepted solution]

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]

Hello @Mindslayer,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[v-ssriganesh]

Hello @Mindslayer,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[v-ssriganesh]

Hello @Mindslayer, 

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.