Forum Discussion

Pfoster's avatar
Pfoster
Resolver I
1 year ago
Solved

Finding comparable products

Following issue: I have a table (Visual) where I have Customer-Product combinations to improve in terms of Margin (created via a ranking...). Now, the user should be able to click in this visual on ...
Need Help
Show and Tell
Tips and Tricks
  • v-hashadapu's avatar
    v-hashadapu
    1 year ago

    Hi Pfoster , Thank you for reaching out to the Microsoft Community Forum.

     

    Please refer attached .pbix file with working solution for your reference and share your thoughts.

     

    If this helped solve the issue, please consider marking it “Accept as Solution” and giving a ‘Kudos’ so others with similar queries may find it more easily. If not, please share the details, always happy to help.
    Thank you.

burakkaragoz's avatar
burakkaragoz
Super User
1 year ago

Hi Pfoster ,

 

You're close. The issue is that SELECTEDVALUE inside a measure doesn't always behave as expected when used in a visual context that returns multiple rows. To get the top 3 comparable products excluding the selected one, you’ll want to restructure the logic a bit.

Here’s a cleaner approach:

1. Create a separate measure to capture the selected Vergleichsschlüssel:

SelectedKey =
MAX(Artikel[Vergleichsschluessel])

This works better than SELECTEDVALUE when used in visuals with single selection.

2. Then use that in your TopN calculation:

TopVergleichsartikel =
VAR SelectedKey = [SelectedKey]
RETURN
CALCULATETABLE(
    TOPN(
        3,
        FILTER(
            ALL(Artikel),
            Artikel[Vergleichsschluessel] = SelectedKey &&
            NOT Artikel[ArtikelNr] IN VALUES(Artikel[ArtikelNr])
        ),
        [Measure]
    )
)

3. For the average of all comparable products:

AvgVergleichsartikel =
VAR SelectedKey = [SelectedKey]
RETURN
CALCULATE(
    AVERAGE([Measure]),
    FILTER(
        ALL(Artikel),
        Artikel[Vergleichsschluessel] = SelectedKey &&
        NOT Artikel[ArtikelNr] IN VALUES(Artikel[ArtikelNr])
    )
)

Make sure your visuals are set up to allow single selection on the main table so the context flows correctly.

 

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI