Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jnickell
Helper V
Helper V

How to determine "Row" context for grand total row

I have a heirarchy, and I would like to have a single measure that changes the calculation method based on how the heirarchy is being used.  Specifically I'd like to "know" what the Row context was when I get to the grand total row.

 

At this stage I'm only interested at the first two levels of the heirarchy (Job, Manufacturing Order)

 

The heirarchy is basically this

  • Job
    • Manufacturing Order
      • Activity

We are trying to provide a performance value at the different levels based on planned time, actual time and a weighting within the overall total work (planned and actual). 

 

I'm trying to handle 3 different scenarios:

  • Viewing performance at the Job Level (level 1 of heirarchy)
  • Viewing performance at the Job > MO level (Levels 1 and 2 of the heirarchy; drill down)
  • Viewing performance at the MO level (Level 2 only; drill through)

 

I have basically constructed 4 Measures at this point.

Measure 1 : Calculates correctly for scenarios 1 & 2, except for the grand total which resolves to "1" due to the way the measure is written
Measure 2: Calculates correctly for Scenario 1 & 2, including the grand total. It is basically a SUMX wrapped around a Summarize of the Level 1 dimension; using the same logic as was written for Measure 1
Measure 3: Calculates correctly for Scenario 3, including the grand total. It is basically a SUMX wrapped around a Summarize of the Level 2 dimension; using the same logic as was written for Measure 1

Measure 4: Currently is using "ISINSCOPE" to switch between Measures 2, and 3, but it fails to do this correctly on the grand total row.

 

This seems like I have done something incorrectly or at least could be done more elegantly.  But mainly my question at this point, is if there's a way to determine which scenario I'm in and based on that decide which measure to return for the Grand Total row?  

 

This is the construct for Measure 1

 

Mfg Scoring = 
    // Determining MO's weighting within overall planned work and actual work
    VAR PlannedTimeMO = 'Manufacturing Weekly Plans'[Mfg Schd Hours]
    VAR TotalPlannedTime = 
        CALCULATE(
            'Manufacturing Weekly Plans'[Mfg Schd Hours],
            ALL('Task Dimensions')
        )
    VAR PercOfShopWkPlan = Divide(PlannedTimeMO, TotalPlannedTime, 0)
    
    VAR ActualTimeMO = 'All Direct Time'[Total Hours]
    VAR TotalActualTime = 
        CALCULATE('All Direct Time'[Total Hours],
            ALL('Task Dimensions')
        )
    VAR PercOfShopActual = DIVIDE(ActualTimeMO, TotalActualTime, 0)
    VAR MOWeighting = DIVIDE((PercOfShopWkPlan + PercOfShopActual),2,0) 
        
    /////// determining points
    VAR PlanCompletion = DIVIDE(ActualTimeMO, PlannedTimeMO,0)
    VAR PlanAccuracy = if (PlanCompletion <> 0,
            ABS(1- PlanCompletion), BLANK()
        )
    VAR PlanPoints = 
        if (ISBLANK(PlanAccuracy), BLANK(),
            if (PlanAccuracy > .5, 0,
                if (PlanAccuracy > .4, .2,
                    if (PlanAccuracy > .3, .4,
                        if (PlanAccuracy > .2, .6,
                            if (PlanAccuracy > .1, .8, 1)
                        )
                    )
                )
            )
        )
    ///// Return value
    VAR MOScore = PlanPoints * MOWeighting
    RETURN
        MOScore

 

For Measures 2 and 3 the approach is identical except that the Summarization is done on a different table/column

Mfg Score MO LVL = 
    /// this is accurate for the MO Level
    SUMX(
        SUMMARIZE(
            'Task Dimensions',
            'Task Dimensions'[Task / MO],
            "MO Score", 
                --- For length removed it, but it's all of Measure 1's code
                Return 
                    MOScore
        ),
        [MO Score]
    )
    

 If I've commited grievous errors here, please let me know. But again my primary goal is to have the grand total value show 

Measure 2' if I'm in scenario 1 or 2, and Measure 3's value if I'm in scenario 3. 

 

Thank you for taking the time to respond!

6 REPLIES 6
AntrikshSharma
Community Champion
Community Champion

At the grand total only Jobs will return TRUE() for ISINSCOPE.

Antriksh,

  I was able to combine into two measures as I got more testing done.  At the Grand Total level, the value being returned is the value for MO Scope.   At the Grand Total level, there is no filter context for any level of the heirarchy.

 

[Mfg Scoring] is the measure I included earlier.  This measure works except that it always provides the MO Scope total at the grand total level.

_Dev Mfg Score Combine = 
    VAR MOScope = 
        SUMX(
            SUMMARIZE(
                'Task Dimensions',
                'Task Dimensions'[Task / MO],
                "MO Score", [Mfg Scoring]
            ),
            [MO Score]
        )
    
    VAR JobScope = 
        SUMX(
            SUMMARIZE(
                'Job Opportunity Index',
                'Job Opportunity Index'[Full Name],
                "MO Score", [Mfg Scoring]
            ),
            [MO Score]
        )       
    RETURN
        if(ISINSCOPE('Job Opportunity Index'[Full Name]),
       JobScope, MOScope
    )

 

Because ISINSCOPE checks for a single value and also if the column is actively being grouped, can you read this article first before making any amendments to your code:

https://www.sqlbi.com/articles/distinguishing-hasonevalue-from-isinscope/

I'm sorry, but I'm not seeing how these two functions help me with determining which value to show at the Grand Total Level. 

 

When the query is run for the grand total row, neither Job nor Manufacturing Order are in scope.

Similar to the image here from Alberto's article

Distinguishing-HASONEVALUE-from-ISINSCOPE-02

In my visual I'm using a Matrix but both "Category" and "Brand" equivalents are on the rows.  When you get to the Grand total row, I have no "one value", nor do I have "in scope"

Below is an image of "Scenario 2"  "Mfg Scoring" is the 'base' measure.  the _Dev2 are the results of the aggregations.  The combine is using the IF (ISINSCOPE) function against the "Full Name"  row.

 

PBI_Forum.png

What I'm wanting to achieve is in this and scenario 1 for it to show the _Dev2 Mfg Score Job Level in the Grand Total colum, but in scenario 3 the Grand Total row should show the Grand Total value for _Dev2 Mfg Score MO Level.

Anonymous
Not applicable

I don't think it's possible what you want (might be wrong). I'm saying this because the grand total should not depend on which levels you're viewing in the matrix. Why is that? Well, it's because some rows in the matrix can show you one level and some other rows can be expanded and show you a different level. How would you then determine what to show a the grand total? Logically it does not seem to be possible.

@Anonymous I'm coming to that conclusion, but was hoping someone in the forums might know a way.  

For now I've "blanking" the Grand Total row and showing the Grand Total values in a separate Card visual. 

 

I was wondering if there might be a way to count rows (or other approach?) at the Grand Total row and "figure out" how we were slicing the visual.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors