Radar Chart Comparison - Aggregate Data - PowerBI

Question

I am trying to create a radar chart which allows me to aggregate data and allow me to control the aggregations with slicers. I have the following table with data ready prepared in Power Query with all the unpivoting done. The following table is called Sheet1.

ID	Continent	Country	City	Year	Attribute	Value
1	Europe	Germany	Berlin	2021	F1	50
1	Europe	Germany	Berlin	2021	F2	60
1	Europe	Germany	Berlin	2021	F3	76
1	Europe	Germany	Berlin	2021	F4	80
1	Europe	Germany	Berlin	2021	F5	30
2	Africa	Nigeria	Lagos	2022	F1	23
2	Africa	Nigeria	Lagos	2022	F2	11
2	Africa	Nigeria	Lagos	2022	F3	23
2	Africa	Nigeria	Lagos	2022	F4	23
2	Africa	Nigeria	Lagos	2022	F5	30
2	Africa	Nigeria	Lagos	2022	F6	64
3	Asia	Japan	Tokyo	2021	F1	34
3	Asia	Japan	Tokyo	2021	F3	32
4	Asia	Japan	Tokyo	2021	F1	45

Averaging everything for the year is easy, with an extra table named Year, with one column also named Year and 2 rows, 2021 and 2022. This column goes into a slicer.

Year
2021
2022

The category will be Sheet1[Attribute] and the following measure going to the Y Axis:

_value-slicer_Avg = SWITCH(SELECTEDVALUE('Year'[Year]),
                        2021,
                           CALCULATE(AVERAGE(Sheet1[Value]),Sheet1[Year]=2021),
                        2022,
                           CALCULATE(AVERAGE(Sheet1[Value]),Sheet1[Year]=2022)
                    )

This yields the average for a year of choice, when I drop the Year column of the Year table into the slicer

Average

What I want to be able to, is to add another dataset on top of it, which would be easy in case of the data of an ID only (see the Individual Comparison tab of the attached pbix)

Duplicating the table in PowerQuery (no relations needed) as below...

Tables

...I need to create these measures:

_slicer_individual_selection = 
        SELECTEDVALUE(Sheet1_New[ID])

_value-slicer_individual_selection = 
        CALCULATE(AVERAGE(Sheet1[Value]),FILTER(Sheet1, Sheet1[ID] = [_slicer_individual_selection]))

And drop the ID of the Sheet1_New table in another slicer.

Individual Comparison

What I want however, is for the second dataset to be the set of the averages of the IDs selected, and the selection should be available at Continent, Country, City or ID level (see the Aggregate Comparison tab of the pbix file attached).

My attempt is not correct, but for the sake of illustration, here it is:

I have replaced the following measure in the Y Axis of the radar chart:

_value-slicer_individual_selection

with this one

_value-slicer_multiple_selection = 
        CALCULATE(AVERAGE(Sheet1[Value]),FILTER(Sheet1, Sheet1[ID] IN ALLSELECTED(Sheet1_New[ID])))

Failed Attempt of Aggregate Comparison

It also doesn't let me see all the continents, countries, cities or IDs available, I have to keep selecting things to make them show up. I want to be able to show the aggregate set of any selection of IDs based on the supersets available (continent, country, city) as the second set on top of the original aggregate per year. In the above example, I want to see the average for all the data from Tokyo (please note: for 2021 only) as the second set.

Also, I want the 4 slicers on the right-hand side to update, so that I only get to select the ones for 2021 when the selected Year is 2021 and the same for 2022.

Attachments are not supported, please download them from here: https://drive.google.com/drive/folders/1aHS0kFCIUe407bS59VF_MUhrLygc5wfh

Answer 1

To solve your case I'd do following:

1. Remove YEAR table and substitute values in the slicer With Sheet1[Year]. All other slicer make of Sheet1 columns. Substitute Sheet1_New with Attributes. Link Attributes[Attribute] to Sheet1[Attribute]. Use Attributes[Attribute] for category in the chart. If you don't want the link then use TREATAS(), but it will slower the measure. The reason for Attributes[Attribute] is that if you use Sheet1[Attribute] as category in the chart, you may filter Attribute values and it will distroy the chart.
2. Change your _value-slicer_Avg to:

---

 _value-slicer_Avg = 
     CALCULATE(
        AVERAGE(Sheet1[Value])
        ,ALLEXCEPT(Sheet1,Sheet1[Attribute],Sheet1[Year])
    )

3. All your Other measures do like this:

---

 _value-slicer_individual_selection = 
     CALCULATE(
         AVERAGE(Sheet1[Value])
        ,ALLEXCEPT(
            Sheet1
            ,Sheet1[Year]
            ,Sheet1[Attribute]
            ,Sheet1[ID]
        )
    )

4. Make your slicers not influencing Sheet1[Year] slicer and each other except chart. Sheet1[Year] should filter all visuals. Chart should not infuence any sclicer. look at pictures

You can create table with unique values of Attibutes as follows:

Attributes = VALUES(Sheet1[Attribute])

I didn't link it with a Sheet1, so TREATAS was added. Below the measure which works with multiple selection.

_Avg = 
    CALCULATE(
        AVERAGE(Sheet1[Value])
        ,TREATAS(Attributes,Sheet1[Attribute])
    )

This measure will show average in respect of year only

_value-slicer_Avg = 
    CALCULATE(
        AVERAGE(Sheet1[Value])
        ,ALLEXCEPT(Sheet1,Sheet1[Year])
        ,TREATAS(Attributes,Sheet1[Attribute])
    )

Regarding slicer look at the picture below:

Try to adjust filtering of slicers and the chart as you need.