Dynamic categories (category changed/recalculated based on slicer choices)

[CJonsson]

Hi!

We have a dataset with Z-scores for different indexes. What we want to do is create an average per country, and then split these averages into four different categories according to the below split. 

This category should be dynamic, to reflect the average and subsequent category for only the selected indexes. 

SWITCH (

        TRUE(),

        WeightedIndexISO >= 0.675, "1",

        WeightedIndexISO >= -0.6745, "2",

        WeightedIndexISO >= -1.349, "3",

        WeightedIndexISO < -1.349, "4",

        "ERROR"

Our isssue is that we are not able to make the category dynamic. It just shows one single bucket based on the average for all indexes and countries, no matter the selection in slicers. 

For example. When having all indexes selected Mexico will have an average Z score of -0.50 and end up in bucket 2. 

But when selecting only index OCI Criminal Markets the weighted Z score is -2.48 and it should end up in bucket 3. However this doesn't work, it still says bucket 2.

Does anyone know how to create a measure for the buckets that is updated accordingly when selecting specific indexes?

I have added a table with sample data as I cannot attach a file in this post. 

ISO_master	Country	Name	Value
FIN	Finland	WWF Biodiversity Risk	-1.63984192031225e-02
FIN	Finland	Corruption Perception Index	2.35900749904752
FIN	Finland	WGI Control of Corruption	1.69796487970888
FIN	Finland	WGI Government Effectiveness	1.6050756606592
FIN	Finland	WGI Political Stability No Violence	1.021483657793
FIN	Finland	WGI Regulatory Quality	1.61987738724371
FIN	Finland	WGI Rule of Law	1.71856732295757
FIN	Finland	WGI Voice and Accountability	1.66894695148963
FIN	Finland	Organised Crime Index Criminality avg.	1.53129131803688
FIN	Finland	Organised Crime Index Resilience avg.	2.327153193201
FIN	Finland	UN Gender Development Index	0.632950692278289
FIN	Finland	UN Gender Inequality Index	1.57974687551886
FIN	Finland	UN Human Development Index	1.45641292235099
FIN	Finland	UN Inequality-adjusted Human Development Index	1.54243873110673
FIN	Finland	UN Planetary pressures-adjusted Human Development Index	0.847762965542295
FIN	Finland	Organised Crime Index Criminal Markets avg.	1.22544881018327
IND	India	WWF Biodiversity Risk	-1.28684437009771
IND	India	Corruption Perception Index	-0.159569411848747
IND	India	WGI Control of Corruption	-0.196085634193209
IND	India	WGI Government Effectiveness	0.453578519938367
IND	India	WGI Political Stability No Violence	-0.876123125262151
IND	India	WGI Regulatory Quality	3.14281131669952e-02
IND	India	WGI Rule of Law	0.177864201703347
IND	India	WGI Voice and Accountability	-2.51527244560662e-02
IND	India	Organised Crime Index Criminality avg.	-0.536460573897385
IND	India	Organised Crime Index Resilience avg.	0.368851031097534
IND	India	UN Gender Development Index	-1.49129358229469
IND	India	UN Gender Inequality Index	-0.738810852199814
IND	India	UN Human Development Index	-0.581279383253759
IND	India	UN Inequality-adjusted Human Development Index	-0.652279470432859
IND	India	UN Planetary pressures-adjusted Human Development Index	-0.246591439427658
IND	India	Organised Crime Index Criminal Markets avg.	-1.39278917669132
MEX	Mexico	WWF Biodiversity Risk	-0.611919958684961
MEX	Mexico	Corruption Perception Index	-0.641850096913989
MEX	Mexico	WGI Control of Corruption	-1.11882816035763
MEX	Mexico	WGI Government Effectiveness	-0.260025268024007
MEX	Mexico	WGI Political Stability No Violence	-0.973436296987146
MEX	Mexico	WGI Regulatory Quality	-0.114449942919375
MEX	Mexico	WGI Rule of Law	-1.0060444856019
MEX	Mexico	WGI Voice and Accountability	-0.27428512617622
MEX	Mexico	Organised Crime Index Criminality avg.	-1.89505567978921
MEX	Mexico	Organised Crime Index Resilience avg.	-0.36932517330907
MEX	Mexico	UN Gender Development Index	0.632950692278289
MEX	Mexico	UN Gender Inequality Index	0.179480064030121
MEX	Mexico	UN Human Development Index	0.248399894272606
MEX	Mexico	UN Inequality-adjusted Human Development Index	0.119838258301552
MEX	Mexico	UN Planetary pressures-adjusted Human Development Index	0.605569777557141
MEX	Mexico	Organised Crime Index Criminal Markets avg.	-2.48435778346411
ZAF	South Africa	WWF Biodiversity Risk	-7.59505731513069e-02
ZAF	South Africa	Corruption Perception Index	1.19081650633412e-03
ZAF	South Africa	WGI Control of Corruption	-0.179897147002125
ZAF	South Africa	WGI Government Effectiveness	-6.54060292779549e-02
ZAF	South Africa	WGI Political Stability No Violence	-1.03831170108231
ZAF	South Africa	WGI Regulatory Quality	-0.19549324915312
ZAF	South Africa	WGI Rule of Law	0.145428302339864
ZAF	South Africa	WGI Voice and Accountability	0.639200128213896
ZAF	South Africa	Organised Crime Index Criminality avg.	-1.60392815709811
ZAF	South Africa	Organised Crime Index Resilience avg.	0.496964256655704
ZAF	South Africa	UN Gender Development Index	-4.98421102630261e-02
ZAF	South Africa	UN Gender Inequality Index	-0.307569261705094
ZAF	South Africa	UN Human Development Index	-5.02846456368857e-02
ZAF	South Africa	UN Inequality-adjusted Human Development Index	-0.673433380809144
ZAF	South Africa	UN Planetary pressures-adjusted Human Development Index	0.103243165439786
ZAF	South Africa	Organised Crime Index Criminal Markets avg.	-1.52255607399998

[CJonsson]

Thank you! That worked and the category is dynamic now. 

One follow-up question. I would like to have a table showing the different categories and how many countries place within each category (still dynamic based on which indexes I choose). However, when placing the DynamicCategory in a table it will just show one category - calculating the average for all countries combined.

Is it possible to group this by country?

[123abc]

Certainly! If you want to display the count of countries within each category dynamically based on the selected indexes, you can create another measure to count the distinct countries within each category. Here's how you can do it:

Step 1: Create a Measure to Count Countries by Category

Countries by Category =
COUNTROWS(
FILTER(
VALUES('YourTableName'[Country]),
[Dynamic Category] = SELECTEDVALUE('YourTableName'[Dynamic Category])
)
)

Step 2: Build the Table

Now, you can create a table visual with two columns:

• Category: Drag the Dynamic Category measure to this column.
• Country Count: Drag the Countries by Category measure to this column.

Ensure that the table visual is not applying any additional filters or slicers that might interfere with the dynamic calculation of the measures.

This table will dynamically display the count of countries within each category based on the selected indexes. It should update as you change the selections in your slicers or filters.

Adjust the column names and formatting as per your preferences. This setup allows for a dynamic display of country counts within each category.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[CJonsson]

I am not quite getting this to work. 

I cannot select the DynamicCategory as SELECTEDVALUE. Don't know if that is because it is a measure?

[123abc · Accepted solution]

To create dynamic categories based on slicer choices in Power BI, you need to use measures that respond to the current filter context. In your case, you want to categorize countries based on the average Z-score of selected indexes. You can achieve this using a DAX measure that calculates the average Z-score for the selected indexes and then assigns the category accordingly.

Here's a general approach to achieve this:

1. Calculate the Average Z-score for Selected Indexes: You need to create a measure that calculates the average Z-score for the selected indexes.

2. Assign Categories Dynamically: Use a SWITCH statement in another measure to assign categories dynamically based on the average Z-score.

Here's how you can implement these steps:

Step 1: Calculate the Average Z-score for Selected Indexes

AverageZScore =
VAR SelectedIndexes = VALUES('YourIndexColumn')
RETURN
AVERAGEX(
FILTER(
'YourTable',
'YourIndexColumn' IN SelectedIndexes
),
[Value]
)

Step 2: Assign Categories Dynamically

DynamicCategory =
VAR AvgZScore = [AverageZScore]
RETURN
SWITCH (
TRUE(),
AvgZScore >= 0.675, "1",
AvgZScore >= -0.6745, "2",
AvgZScore >= -1.349, "3",
AvgZScore < -1.349, "4",
"ERROR"
)

Notes:

• Replace 'YourIndexColumn' with the appropriate column containing index names.
• Replace 'YourTable' with the name of your table.
• Replace '[Value]' with the column name that contains the Z-scores.
• Ensure that the slicer visuals are set up to filter the appropriate columns in your data model.

These measures should dynamically respond to slicer selections, recalculating the average Z-score and assigning categories accordingly. Make sure your visuals are configured to react to these measures appropriately.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.