cancel
Showing results for
Did you mean:
Highlighted
Helper III

## 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],
)
VAR PercOfShopWkPlan = Divide(PlannedTimeMO, TotalPlannedTime, 0)

VAR ActualTimeMO = 'All Direct Time'[Total Hours]
VAR TotalActualTime =
CALCULATE('All Direct Time'[Total Hours],
)
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(
"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
Highlighted
Super User II

## Re: How to determine "Row" context for grand total row

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

Thank you,
Antriksh Sharma
Highlighted
Helper III

## Re: How to determine "Row" context for grand total row

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

Highlighted
Super User II

## Re: How to determine "Row" context for grand total row

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/

Thank you,
Antriksh Sharma
Highlighted
Helper III

## Re: How to determine "Row" context for grand total row

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

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.

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.

Highlighted
Solution Sage

## Re: How to determine "Row" context for grand total row

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.
Highlighted
Helper III

## Re: How to determine "Row" context for grand total row

@daxer 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.