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
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:
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!
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 )
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.
@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.
This will be a conference that you do not want to miss!
Don't miss the Power BI Dev Camp this week!
Check out a full recap of the month!
Check out the winners of the recent 'Can You Solve These?' community challenge!
Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.