How to show average of item values with the right filtering

[lafakios]

Hi,

I would like to create a visual based on the below data which should show the average Param value of the three Item_IDs (with have the highest SortKey) of an Item . The Item shall be selected with a page slicer.

I added the two below measures:

ReleaseRank =

RANKX(ALLSELECTED('item_values'), CALCULATE(SUM('item_values'[SortKey])), ,DESC, Dense)

Average_Of_The_Three_Latest_ItemIDs_of_An_Item =

VAR latest = CALCULATE(MAX('Item_values'[Param]), FILTER(ALLSELECTED('Item_values'), 'Item_values'[ReleaseRank] = 1))
VAR secondlatest = CALCULATE(MAX('Item_values'[Param]), FILTER(ALLSELECTED('Item_values'), 'Item_values'[ReleaseRank] = 2))
VAR thirdlatest = CALCULATE(MAX('Item_values'[Param]), FILTER(ALLSELECTED('Item_values'), 'Item_values'[ReleaseRank] = 3))
return IF(AND(NOT(ISBLANK(secondlatest)), NOT(ISBLANK(thirdlatest))),
              (latest + secondlatest + thirdlatest) / 3,
                  IF(AND(NOT(ISBLANK(secondlatest)), ISBLANK(thirdlatest)),
                     (latest + secondlatest) / 2,
                        latest))  

The visual does provide the average value of the selected Item in the slicer, however it also includes all other Items which are present in the 'item_values' Table. I have tried different alternatives in the Calculate-filter for only showing the selected Item's average without any luck.

I believe that the above measure formulas and logic could also be written in a better way. 

Thanks for your support.

Regards,

Akis

'item_values' Table

Item_ID	Item	Param	SortKey
ItemA 1.4	ItemA	0	1040000
ItemA 1.3	ItemA	1	1030000
ItemA 1.2	ItemA	2	1020000
ItemA 1.1	ItemA	3	1010000
ItemA 1.0	ItemA	5	1000000
ItemB 1.3	ItemB	20	1030000
ItemB 1.2	ItemB	2	1020000
ItemB 1.1	ItemB	3	1010000
ItemB 1.0	ItemB	16	1000000

[ArwaAldoud · Accepted solution]

Hi @lafakios  try this 

ReleaseRank =

VAR CurrentItem = SELECTEDVALUE('item_values'[Item])

RETURN

    IF(

        NOT(ISBLANK(CurrentItem)), -- Ensures an item is selected

        RANKX(

            FILTER(

                ALLSELECTED('item_values'),

                'item_values'[Item] = CurrentItem

            ),

            CALCULATE(MAX('item_values'[SortKey])), -- Ensures a single value per row

            ,

            DESC,

            DENSE

        )

    )

Average_Of_The_Three_Latest_ItemIDs_of_An_Item =

VAR CurrentItem = SELECTEDVALUE('item_values'[Item])

VAR Top3 =

    TOPN(3,

        FILTER(

            ALLSELECTED('item_values'),

            'item_values'[Item] = CurrentItem

        ),

        'item_values'[SortKey], DESC

    )

VAR Top3Avg = AVERAGEX(Top3, 'item_values'[Param])

RETURN

    IF(NOT(ISBLANK(CurrentItem)), Top3Avg)

If this response was helpful, please accept it as a solution and give kudos to support other community members.

[ArwaAldoud]

here pbix file 

[lafakios]

Thank you very much for the support provided!

The two provided formulas did deliver the wanted results.

Regards,

Akis

[ArwaAldoud]

You're very welcome @lafakios  😊 I'm glad that it helped you.