Summing a measure

Question

Hi,&nbsp;I have a dataset on a list of assets which will need to be replaced as time goes on. Each has a different lifecycle so they will expire at different times. The table below is&nbsp;a sample and contains the asset, when it will expire and how much it will cost.&nbsp;Assets_MedicalMain_asset_numberAssetReplacementYearAssetValueInflationCustom1173642000$187,6357/01/20001173642018$267,9897/01/20181173642036$382,7547/01/20362424662005$2,095,1407/01/20052424662019$2,764,4927/01/20192424662033$3,647,6897/01/20332424662047$4,813,0487/01/2047&nbsp;&nbsp;I am trying to calculate the total value of all of the assets over time.&nbsp;When pivoted against the&nbsp;Fiscal Year only the years which the asset is to be placed are dispayed (as shown in red).&nbsp;However, I need to see the value of the asset every year, not just when the asset is replaced. I have created a measure which shows this (as shown in blue).&nbsp;The issues I have is the total. I need it to be the sum of all of the assets, not the most recent change as is currently displayed. For instance&nbsp;2006/2007 should be $187,635 + $2,095,140.&nbsp;How would I change my measure to show this?&nbsp;&nbsp;Measure = 
VAR SelectedDate = LASTNONBLANK(DimDate[Date],1)

VAR LastDateOfChange = CALCULATE(
    MAX(DimDate[Date]),
    FILTER(
        ALL(DimDate), 
        DimDate[Date] &lt;= SelectedDate &amp;&amp;
        [Changes] = 1
))

VAR AIV = 
CALCULATE(
    SUM(Assets_Medical[AssetValueInflation]), 
    DimDate[Date] = LastDateOfChange,
    ALLSELECTED(DimDate)
)

RETURN AIV&nbsp;Changes = 
IF(ISBLANK(LASTNONBLANK(Assets_Medical[AssetValueInflation],1)), 0,1)Thanks for your help!

liviolanzo · Answer

Hi&nbsp;Anonymous&nbsp;you need to create an iteration with SUMX over the main_asset_number, i can-t provide the exact measure as i dont have access to your data to test it.&nbsp;but something which would make your measure (and your life) much easier, would be to modify your model to something like the below, and your measure would become a simple sum&nbsp;for the first&nbsp;asset number:&nbsp;&nbsp;

v-cherch-msft · Answer

Hi&nbsp;Anonymous
&nbsp;
You may have a look at this post.It's better that&nbsp;if you could share your sample data which could reproduce&nbsp;your scenario and your desired output.You can also upload the pbix file to OneDrive and post&nbsp;the link here. Do mask sensitive data before uploading.
&nbsp;
Regards,
Cherie

anonymous · Answer

thanks v-cherch-msft! I have had a look at the post and tried to implement however I am still having trouble. I have uploaded a sample dataset here with the measures I currently have.

Thanks again for your help!

anonymous · Answer

Thanks&nbsp;LivioLanzo! I agree regarding the change in the model however unfortuantely we have far too many assets for this approach to be feasible.&nbsp;I have tried to iterate over the SUMX as follows however am still getting some funky resultsAllItems = 
SUMX(
VALUES(Assets_Medical[Main_asset_number]), [Measure])I have uploaded a sample dataset and if you are able/willing to help further that would be greatly appreciated.&nbsp;Many ThanksBen

v-cherch-msft · Answer

Hi&nbsp;Anonymous
&nbsp;
You may check below measure:
Measure 2 = 
VAR myTable =
    CALCULATETABLE (
        ALL ( Assets_Medical[Main_asset_number] ),
        ALLEXCEPT ( DimDate, DimDate[FiscalYearText] )
    )
RETURN
    IF (
        HASONEFILTER ( Assets_Medical[Main_asset_number] ),
        [Measure],
        SUMX ( myTable, [Measure] )
    )

Regards,
Cherie

Forum Discussion

Summing a measure

7 Replies

Related content

Measure to sum MTD for ALL months

Rolling SUM / Cumulative SUM for measure

Summing a measure

Average of Sum Measure

Running sum

Main_asset_number	AssetReplacementYear	AssetValueInflation	Custom
117364	2000	$187,635	7/01/2000
117364	2018	$267,989	7/01/2018
117364	2036	$382,754	7/01/2036
242466	2005	$2,095,140	7/01/2005
242466	2019	$2,764,492	7/01/2019
242466	2033	$3,647,689	7/01/2033
242466	2047	$4,813,048	7/01/2047