Summarizing measures

[Anonymous]

All the data, calcs and visuals described below are in the pbix file linked here

https://hartwhite-my.sharepoint.com/:u:/g/personal/paul_hartwhite_onmicrosoft_com/EY2H_HP1yflMmaVfmd...

I have two assets. Each has some attributes then a year it will reach a certain condition grade

I've used union and summarize to restructure this data and display the deterioration over time on a line chart

Up to this stage all the data is columns loaded in Power Query 

A user is able to spend a % of money at Condition Grade 3 and 4 to slow the asset deterioration down. I've added 2 parameters and sliders for the user to do this

The following is all achieved through measures

The % spend selected is turned into an adjustment %

Which is used to adjust the CG3, 4 and 5 intervals

I'm unable to do the create a summarize table on these measures - the new table just gives a single value for all assets

I tried another approach of using the first summarize table and modifiying this. I offset the date, calcuate the change, calculate the adjustment, then bring this all together for the new date

The Adjusted Date works and is correct but as a measure I can't use it as the axis of the line chart

Any ideas how I can make this dynamic but at the same time use the results in charts and further calculations?

Thank you in advance

[lbendlin]

"I'm unable to do the create a summarize table on these measures - the new table just gives a single value for all assets"

You cannot measure a measure.  To get your result you need to create an independent measure that looks at the data from the perspective of the grand total, not from the perspective of the individual value. Often that includes using one of the X functions. Can you formulate the expected outcome?

[Anonymous]

Thanks for the reponse and apologies for the delay in responding. I've tried to develop what i'm trying to do but also simplify the issue i'm having

Here is my table of information

As a brief overview of the data,

• An asset can have 3 maintenance regimes and 3 derterioration rates - 9 scenarios in total.
• Each of those scenarios has a deterioration curve, a number of years before the asset reaches a certain Condition Grade (CG)
• A user can chose to spend a % of money at CG3 and CG4 to slow down that deterioration
• The table displays 3 options, Do Nothing (0% Spend), Baseline (a fixed % spend coming from Excel) and Adjusted (user selected via PBI parameters)

The first 15 columns (AssetID to CG 5 Baseline) are fed from excel sheets and so are constructed in Power Query

I'm using a summarize to create a new table which I can graph

Deterioration Curve =

Union

(

SUMMARIZE('Assets v2','Assets v2'[Maintenance Regime],'Assets v2'[Deterioration Rate],"Model","Do Nothing","Condition Grade",1,"Year",AVERAGE('Assets v2'[CG 1])),

SUMMARIZE('Assets v2','Assets v2'[Maintenance Regime],'Assets v2'[Deterioration Rate],"Model","Do Nothing","Condition Grade",2,"Year",AVERAGE('Assets v2'[CG 2])),

SUMMARIZE('Assets v2','Assets v2'[Maintenance Regime],'Assets v2'[Deterioration Rate],"Model","Do Nothing","Condition Grade",3,"Year",AVERAGE('Assets v2'[CG 3])),

SUMMARIZE('Assets v2','Assets v2'[Maintenance Regime],'Assets v2'[Deterioration Rate],"Model","Do Nothing","Condition Grade",4,"Year",AVERAGE('Assets v2'[CG 4])),

SUMMARIZE('Assets v2','Assets v2'[Maintenance Regime],'Assets v2'[Deterioration Rate],"Model","Do Nothing","Condition Grade",5,"Year",AVERAGE('Assets v2'[CG 5])),

SUMMARIZE('Assets v2','Assets v2'[Maintenance Regime],'Assets v2'[Deterioration Rate],"Model","Baseline","Condition Grade",1,"Year",AVERAGE('Assets v2'[CG 1])),

SUMMARIZE('Assets v2','Assets v2'[Maintenance Regime],'Assets v2'[Deterioration Rate],"Model","Baseline","Condition Grade",2,"Year",AVERAGE('Assets v2'[CG 2])),

SUMMARIZE('Assets v2','Assets v2'[Maintenance Regime],'Assets v2'[Deterioration Rate],"Model","Baseline","Condition Grade",3,"Year",AVERAGE('Assets v2'[CG 3 Baseline])),

SUMMARIZE('Assets v2','Assets v2'[Maintenance Regime],'Assets v2'[Deterioration Rate],"Model","Baseline","Condition Grade",4,"Year",AVERAGE('Assets v2'[CG 4 Baseline])),

SUMMARIZE('Assets v2','Assets v2'[Maintenance Regime],'Assets v2'[Deterioration Rate],"Model","Baseline","Condition Grade",5,"Year",AVERAGE('Assets v2'[CG 5 Baseline]))

)

The remaining 7 columns (CG3 Adjusted Spend % to CG 5 Adjusted) are measures all calculating from parameter sliders

Turning the parameter into a %

CG3 Adjusted Spend % = 'CAPEX CG3'[CAPEX CG3 Value]/100

CG4 Adjusted Spend % = 'CAPEX CG4'[CAPEX CG4 Value]/100

Looking up what that % equates to as an adjustment

CG3 Adjustment % = LOOKUPVALUE('Spend Adjustment'[CG3 Adjustment],'Spend Adjustment'[Spend],[CG3 Adjusted Spend %]*100,'Spend Adjustment'[Maintenance Regime Number],[Maintenance Regime Number])/100

 CG4 Adjustment % = LOOKUPVALUE('Spend Adjustment'[CG4 Adjustment],'Spend Adjustment'[Spend],[CG4 Adjusted Spend %]*100,'Spend Adjustment'[Maintenance Regime Number],[Maintenance Regime Number])/100

Applying that adjustment to the Do Nothing numbers

CG 3 Adjusted = CALCULATE(Min('Assets v2'[CG 3]) + (Min('Assets v2'[CG 3])-Min('Assets v2'[CG 2])) * [CG3 Adjustment %])

CG 4 Adjusted = CALCULATE(([CG 3 Adjusted] + (Min('Assets v2'[CG 4])-Min('Assets v2'[CG 3]))) + (Min('Assets v2'[CG 4])-Min('Assets v2'[CG 3])) * [CG4 Adjustment %])

CG 5 Adjusted = [CG 4 Adjusted] + (Min('Assets v2'[CG 5])-Min('Assets v2'[CG 4]))

Is there any way I can get the measure generated numbers onto the same graph as the power query generated numbers?

[lbendlin]

oops - where did the Power Query comment come from all of a sudden?

And no, you cannot mix measures and columns in the values area that way. It has to be turtles measures all the way down. As you can see in your current version the chart is not impacted by the What-If parameters at all.

I'll try working on a measure based version but it may take a while.

[lbendlin · Accepted solution]

Here is my proposal.  There is one potentially big compromise - you cannot have a measure as the x-axis of a standard line chart, so I had to rotate the chart by 90 degrees.

see attached.

[Anonymous]

Perfect - thank you