Sum of recent values based on years and Ranking

[Anonymous]

Hi

I have the following data set wherein Assets are priced in 2018 and 2019 (some are only values in one year like Asset D in 2018 and C in 2019, whereas, some are repeating in both the years like Asset A, B and E). I am looking to get the sum of all assets in one card but only want the latest value for the repeating assets. For example i do not want the values of assets A, B and E for 2018 in the sum.

Asset Name	Location	Value	Year
A	California	50	2018
B	Washington	60	2018
D	New York	45	2018
E	New Mexico	68	2018
A	California	55	2019
B	Washington	66	2019
C	Delaware	70	2019
E	New Mexico	65	2019

The total should be 301. I think it can be done using ranking but not clear on the process.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Thanks @Ashish_Mathur 

In the measure created 'Total of value in recent years' i am unable to understand the refrence if 'ABCD' not sure from where it is coming from.

[Ashish_Mathur]

You are welcome.  The SUMMARIZE() function requires the 3rd input to be a column heading.  So ABCD is just that heading within double quotes.  It can be any other descriptive heading you want.

If my previous reply helped, please mark it as Answer.   

[edhans]

For some reason @CNENFRNL the data lineage using DISTINCT() isn't the first thing that comes to my mind...

One difference though is yours will not allow for multiple values in the same year. My longer measure will. I am not sure if @Anonymous can guarantee one value per asset per year.

Modifying @CNENFRNL's measure this way will do a sum.

New Measure = 
SUMX(
    DISTINCT('Table'[Asset Name]),
    VAR varYear = 
        CALCULATE(
            MAX('Table'[Year])
        )
    RETURN
        CALCULATE(
            SUM('Table'[Value]),
            FILTER(
                ALL('Table'[Year]),
                'Table'[Year] = varYear
            )
        )
    )

 I did the full FILTER vs just the predicate. Habbit. 

[CNENFRNL]

Hi, @Anonymous , Both DAX and Power Query do the trick with ease. You might want to refer to the attached file for details.

DAX measure:

Latest Value = 
SUMX (
    DISTINCT ( Assets[Asset Name] ),
    VAR __yr =
        CALCULATE ( MAX ( Assets[Year] ) )
    RETURN
        CALCULATE ( MAX ( Assets[Value] ), Assets[Year] = __yr )
)

PQ solution:

[edhans]

Hi @Anonymous - there might be a simpler way to do this, but I took the long and straighforward route.

Measure2 = 
VAR varGroupedData = 
ADDCOLUMNS(
    SUMMARIZE(
        'Table',
        'Table'[Asset Name]
    ),
    "Summary Values",
        CALCULATE(
        	VAR varAsset = MAX('Table'[Asset Name])
            VAR varMaxYear =
                MAXX(
                    FILTER(
                        ALL(
                            'Table'[Year],
                            'Table'[Asset Name]
                        ),
                        'Table'[Asset Name] = varAsset
                    ),
                    'Table'[Year]
                )
            RETURN
                SUMX(
                    FILTER(
                        'Table',
                        'Table'[Year] = varMaxYear
                    ),
                    'Table'[Value]
                )
        )
)
RETURN
    SUMX(
        varGroupedData,
        [Summary Values]
    )

It will return these two visuals if desired:

It does this by building a temporary table that is by asset and the latest values for that asset. So the ADDCOLUMNS(SUMMARIZE()) build this:

Then SUMX just adds it up.

EDIT: slightly cleaned it up. The Asset column in ADDCOLUMNS() wasn't needed. I was just thinking through the issue there.