Forum Discussion

Sjoerd_g's avatar
Sjoerd_g
Frequent Visitor
3 years ago
Solved

Using SELECTEDVALUE with SUMMARIZE

Hi all,   I have a dilemma as I'm trying to create a visual that is seemingly very straightforward but I have tried several approaches, and nothing is what I'd like although I got quite close.   ...
Need Help
  • Sjoerd_g's avatar
    Sjoerd_g
    3 years ago

    I managed to solve this and thought it might be useful for anyone else.

     

    I set up Variables with the initially mentioned measure and used the SWITCH combined with SELECTEDVALUE to test if the name matched and returned the correct VAR.

     

    RETURN
SWITCH(
    TRUE(),
    SELECTEDVALUE(ratings[rating]) = "Atmosphere", _atmosphere,
    SELECTEDVALUE(ratings[rating]) = "Cleanliness", _Cleanliness,
    SELECTEDVALUE(ratings[rating]) = "Drink", _Drink,
    SELECTEDVALUE(ratings[rating]) = "Service", _Service,
    SELECTEDVALUE(ratings[rating]) = "Value", _Value
)

     

    VAR example: 

    VAR _Value = 
AVERAGEX(
    FILTER(
        SUMMARIZE(
            ALLSELECTED(ratings),
            [response id],
            "Atmosphere", CALCULATE(AVERAGE(ratings[score]),SEARCH("Atmosphere", ratings[Translated Rating],,0)),
            "Cleanliness", CALCULATE(AVERAGE(ratings[score]),SEARCH("Cleanliness", ratings[Translated Rating],,0)),
            "Drink", CALCULATE(AVERAGE(ratings[score]),SEARCH("Drink", ratings[Translated Rating],,0)),
            "Food", CALCULATE(AVERAGE(ratings[score]),SEARCH("Food", ratings[Translated Rating],,0)),
            "Service", CALCULATE(AVERAGE(ratings[score]),SEARCH("Service", ratings[Translated Rating],,0)),
            "Value", CALCULATE(AVERAGE(ratings[score]),SEARCH("Value", ratings[Translated Rating],,0))
        ),
        [Food] <= 2
    ),
    [Value]
)

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

Sjoerd_g So wait, why not that same measure just once and have the x-axis be the rating column?

Sjoerd_g's avatar
Sjoerd_g
Frequent Visitor
3 years ago

How exactly do you mean?

 

This would just duplicate the one-column average:

AVERAGEX(
    FILTER(
        SUMMARIZE(
            ALLSELECTED(ratings),
            [response id],
            "Atmosphere", CALCULATE(AVERAGE(ratings[score]),SEARCH("Atmosphere", ratings[Translated Rating],,0)),
            "Cleanliness", CALCULATE(AVERAGE(ratings[score]),SEARCH("Cleanliness", ratings[Translated Rating],,0)),
            "Drink", CALCULATE(AVERAGE(ratings[score]),SEARCH("Drink", ratings[Translated Rating],,0)),
            "Food", CALCULATE(AVERAGE(ratings[score]),SEARCH("Food", ratings[Translated Rating],,0)),
            "Service", CALCULATE(AVERAGE(ratings[score]),SEARCH("Service", ratings[Translated Rating],,0)),
            "Value", CALCULATE(AVERAGE(ratings[score]),SEARCH("Value", ratings[Translated Rating],,0))
        ),
        [Food] <= 2
    ),
    [Cleanliness]
)

 

And when I remove the Averagex part:

    FILTER(
        SUMMARIZE(
            ALLSELECTED(ratings),
            [response id],
            "Atmosphere", CALCULATE(AVERAGE(ratings[score]),SEARCH("Atmosphere", ratings[Translated Rating],,0)),
            "Cleanliness", CALCULATE(AVERAGE(ratings[score]),SEARCH("Cleanliness", ratings[Translated Rating],,0)),
            "Drink", CALCULATE(AVERAGE(ratings[score]),SEARCH("Drink", ratings[Translated Rating],,0)),
            "Food", CALCULATE(AVERAGE(ratings[score]),SEARCH("Food", ratings[Translated Rating],,0)),
            "Service", CALCULATE(AVERAGE(ratings[score]),SEARCH("Service", ratings[Translated Rating],,0)),
            "Value", CALCULATE(AVERAGE(ratings[score]),SEARCH("Value", ratings[Translated Rating],,0))
        ),
        [Food] <= 2
    )

We get a measure returning multiple values, resulting in the following error: