I have a table called Data with many columns. I have setup the dashboard so far so that changes on the slicers in this way:
1. Slicer1: Variant will choose a column from Data corresponding to variant
2. Slicer2: Group, Measure_Name - allows the user to select which of the particular numeric columns in the Data table to display on the graphs. I did this using a bit of an ugly work-around with SWITCH.
3. Slicer3: Attribute, Value, Country - will show the particular regions (countries)
So far my line graph responds nicely to changes in any of the slicers and the shape map responds to changes in the regions. See below.
What im trying to do is change the Legend of the Shape Map so that depending on my selections in each of the 3 slicers I will get a table of |Countries (corresponding to slicer 3)|Average of Metric (corresponding to slicer 2)| which I hope I will be able to get appropriate shading in the Shape Map.
I've tried the DAX code below to create the table but dont seem to be getting anywhere - really any help with be HUGELY appreciated please as Im really new to Power BI.
table2 =
VAR _DS0FilterTable0 = SELECTEDVALUE('Data'[Variant])
VAR _DS0FilterTable1 = SELECTEDVALUE('Measure_Selection1'[Group])
VAR _DS0FilterTable2 = SELECTEDVALUE('Measure_Selection1'[Data_Field])
VAR _DS0FilterTable3 = SELECTEDVALUE('Data'[Year])
// Ensure _DS0FilterTable0 is defined correctly
VAR _SelectedYears = VALUES('Data'[Year])
VAR _MaxYear = MAXX(
FILTER(ALL('Data'[Year]), 'Data'[Year] IN SELECTCOLUMNS(SUMMARIZE(_SelectedYears, 'Data'[Year]), "Year", 'Data'[Year])),
'Data'[Year]
)
VAR _MinYear = MINX(
FILTER(ALL('Data'[Year]), 'Data'[Year] IN SELECTCOLUMNS(SUMMARIZE(_SelectedYears, 'Data'[Year]), "Year", 'Data'[Year])),
'Data'[Year]
)
// Define selected metric based on user selection
VAR _Selected_Metric =
IF(
HASONEVALUE(Measure_Selection1[Measure_Name]),
SWITCH(
VALUES(Measure_Selection1[Measure_Name]),
"Tot_Pop_1Jan_000s",SUM(Data[Tot_Pop_1Jan_000s]),"Tot_Pop_1Jul_000s",SUM(Data[Tot_Pop_1Jul_000s]),"Male_Pop_1Jul_000s",SUM(Data[Male_Pop_1Jul_000s]),"Female_Pop_1Jul_000s",SUM(Data[Female_Pop_1Jul_000s]),"Pop_Density",SUM(Data[Pop_Density]),"Pop_Sex_Ratio",SUM(Data[Pop_Sex_Ratio]),"Median_Age",SUM(Data[Median_Age]),"Nat_Change",SUM(Data[Nat_Change]),"Nat_Change_Rate",SUM(Data[Nat_Change_Rate]),"Pop_Change_000s",SUM(Data[Pop_Change_000s]),"Pop_Growth_Rate",SUM(Data[Pop_Growth_Rate]),"Births_000s",SUM(Data[Births_000s]),"Births_Women15_19_000s",SUM(Data[Births_Women15_19_000s]),"Crude_Birth_Rate",SUM(Data[Crude_Birth_Rate]),"Tot_Fertility_Rate",SUM(Data[Tot_Fertility_Rate]),"Net_Production_Rate",SUM(Data[Net_Production_Rate]),"Mean_Childbearing_Age",SUM(Data[Mean_Childbearing_Age]),"Pop_Sex_Ratio_At_Birth",SUM(Data[Pop_Sex_Ratio_At_Birth]),"Tot_Deaths_000s",SUM(Data[Tot_Deaths_000s]),"Male_Deaths_000s",SUM(Data[Male_Deaths_000s]),"Female_Deaths_000s",SUM(Data[Female_Deaths_000s]),"Crude_Death_Rate",SUM(Data[Crude_Death_Rate]),"Life_Exp_At_Birth",SUM(Data[Life_Exp_At_Birth]),"Male_Life_Exp_At_Birth",SUM(Data[Male_Life_Exp_At_Birth]),"Female_Life_Exp_At_Birth",SUM(Data[Female_Life_Exp_At_Birth]),"Life_Exp_At_15Yrs",SUM(Data[Life_Exp_At_15Yrs]),"Male_Life_Exp_At_15Yrs",SUM(Data[Male_Life_Exp_At_15Yrs]),"Female_Life_Exp_At_15Yrs",SUM(Data[Female_Life_Exp_At_15Yrs]),"Life_Exp_At_65Yrs",SUM(Data[Life_Exp_At_65Yrs]),"Male_Life_Exp_At_65Yrs",SUM(Data[Male_Life_Exp_At_65Yrs]),"Female_Life_Exp_At_65Yrs",SUM(Data[Female_Life_Exp_At_65Yrs]),"Life_Exp_At_80Yrs",SUM(Data[Life_Exp_At_80Yrs]),"Male_Life_Exp_At_80Yrs",SUM(Data[Male_Life_Exp_At_80Yrs]),"Female_Life_Exp_At_80Yrs",SUM(Data[Female_Life_Exp_At_80Yrs]),"Infant_Deaths_Under1Yr_000s",SUM(Data[Infant_Deaths_Under1Yr_000s]),"Infant_Mortality_Rate",SUM(Data[Infant_Mortality_Rate]),"Births_Surviving_1Yr",SUM(Data[Births_Surviving_1Yr]),"Deaths_Under_5Yrs_000s",SUM(Data[Deaths_Under_5Yrs_000s]),"Mortality_Under_5Yrs",SUM(Data[Mortality_Under_5Yrs]),"Mortality_Under_40Yrs",SUM(Data[Mortality_Under_40Yrs]),"Male_Mortality_Under_40Yrs",SUM(Data[Male_Mortality_Under_40Yrs]),"Female_Mortality_Under_40Yrs",SUM(Data[Female_Mortality_Under_40Yrs]),"Mortality_Under_60Yrs",SUM(Data[Mortality_Under_60Yrs]),"Male_Mortality_Under_60Yrs",SUM(Data[Male_Mortality_Under_60Yrs]),"Female_Mortality_Under_60Yrs",SUM(Data[Female_Mortality_Under_60Yrs]),"Mortality_Under_15_50Yrs",SUM(Data[Mortality_Under_15_50Yrs]),"Male_Mortality_Under_15_50Yrs",SUM(Data[Male_Mortality_Under_15_50Yrs]),"Female_Mortality_Under_15_50Yrs",SUM(Data[Female_Mortality_Under_15_50Yrs]),"Mortality_Under_15_60Yrs",SUM(Data[Mortality_Under_15_60Yrs]),"Male_Mortality_Under_15_60Yrs",SUM(Data[Male_Mortality_Under_15_60Yrs]),"Female_Mortality_Under_15_60Yrs",SUM(Data[Female_Mortality_Under_15_60Yrs]),"Net_Migrants_000s",SUM(Data[Net_Migrants_000s]),"Net_Migration_Rate",SUM(Data[Net_Migration_Rate]),blank()
)
)
// Calculate average of selected metric for each region/subregion
VAR Avg_Metric_Table =
SUMMARIZE(
FILTER(
ALL('Data'),
'Data'[Variant] = _DS0FilterTable0 && 'Data'[Year] >= _MinYear && 'Data'[Year] <= _MaxYear
),
'Data'[Region, subregion, country or area *],
"Average_Selected_Metric", AVERAGEX(VALUES('Data'[Region, subregion, country or area *]), _Selected_Metric)
)
RETURN
Avg_Metric_Table
