Forum Discussion

Pfoster's avatar
Pfoster
Icon for Resolver I rankResolver 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 an article, and in a second visual, he should get (if existing) the top 3 Customer-Product combination (excluding the selected one) and the total average of all comparable Customer-Product combination. 
I tried to  build a measure like this:

Top3Vergleichsartikel =
VAR AktuellerSchluessel = SELECTEDVALUE(Artikel[VergleichsSchlüssel])
RETURN
    TOPN(
        3,
        FILTER(
            ALL(Artikel),
            Artikel[VergleichsSchlüssel] = AktuellerSchluessel &&
            NOT Artikel[ArtikelNr] IN VALUES(Artikel[ArtikelNr])
        ),
        [Marge],
        DESC
    )

(Vergleichsschlüssel = Key build by EntityCode & Article Code)

But the Selected Value is not working with a Measure, so I am struggeling, how to get the Top3 (and ideally the average of all the "Vergleichsschlüssel"-Combinations). 
Is there a workaround how to get it? 

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.

2 Replies

  • v-hashadapu's avatar
    v-hashadapu
    Icon for Community Support rankCommunity Support
    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
    Icon for Super User rankSuper 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