Creating a KPI Matrix with Calculation Groups

[epresson]

Hello,

I have 10 KPIs that my organization needs to track in a matrix as follows:

Category	KPI Name	Current Value	Target	Trend	Status
1					🔴
	KPI1	50%	90%	🡶	🔴
	KPI2	10	1	🡵	🔴
	KPI3	0.5	0.5	🡲	🟡
2					🔴
	KPI4	1000	50	🡶	🔴
	KPI5	500	25	🡵	🔴
	KPI6	30%	50%	🡶	🔴
	KPI7	80	100	🡲	🟢
3					🔴
	KPI8	2	1	🡶	🔴
	KPI9	1	1	🡵	🟡

Each KPI's current value, target, trend, and status are measures in my dataset with various logic. All of the KPIs need to be grouped into categories with category level statuses which basically display the worst status of all KPIs in the category. 

So far, I've come across two different potential solutions to build such a visual but one has some serious performance issues and the other is not able to meet full requirements. 

Solution 1:

I added a static table in my model with one row per KPI name, category, format string, indicator, order, and active status. The table looks a bit like this:

KPI Name	Indicator	Order	Category	Format	Active
KPI1	Current Value	1	1	0.0%	True
KPI1	Trend	2	1	0	True
KPI1	Status	3	1	0	True
KPI7	Current Value	1	2	0	False
KPI7	Trend	2	2	0	False
KPI7	Status	3	2	0	False

After creating this table, I have created a measure which is basically a HUGE switch statement which defines the conditions to display each measure per row and column in the matrix. I then place the KPI category and KPI Name field as the rows for the matrix and the KPI Indicator as the column field for the matrix and place the switch measure as the value. There are two issue with this solution:

1. it has horrible performance since the switch statement has about 140 lines of DAX
2. The Switch measure is hard to maintain with so much code (adding a new measure to the matrix is a challenge).

Solution 2:

I figured out a way to create the same matrix table via multiple calculation groups. I have created three calculation groups as follows:

1. KPIs - Precedence 4
   1. Calc Item 1 = KPI1 current value measure
   2. Calc Item 2 = KPI2 current value measure
   3. Calc Item 3 = KPI3 current value measure...
2. KPI Indicators - Precedence 3
   
   1. Calc Item 1 = Current Value logic
   2. Calc Item 2 = Target logic
   3. Calc Item 3 = Trend logic...
3. KPI Categories - Precendence 2
   1. Calc Item 1 = Category 1 logic
   2. Calc Item 2 = Category 2 logic
   3. Calc Item 3 = Category 3 logic

The KPIs calc group has one calculation item per KPI name and has the current value measure as the items value. So there are about 10 items in this group.

The KPI indicators group has one item per column needed in the matrix so one for Current Value, Target, Trend, and Status. Each of these items has some specific logic which I was able to generalize from all of the corresponding measures.

The KPI Categories has one item per category needed in the matrix and also with some corresponding logic to do the grouping (I look at the selected measure names to identify if they belong to a specific category). 

This second solution has vastly superior performance compared to the first solution (35 seconds visual refresh for solution 1 vs 3.5 seconds visual refresh for solution 2!). My only problem is that I'm not able to provide the required category level statuses (subtotals are not available in the matrix for some reason) for solution 2 which is an overall requirement. The switch statement in solution 1 is able to provide status per level/subtotal in the hierarchy using the Max and ISINSCOPE function but I can't get these to work when I try them in the KPI Categories or KPI Indicators calc items. Does any one have any suggestions on a way moving forward? Is there maybe a potential hybrid solution to use both the static table and calc groups? Am I missing something in terms of defining hierarchies in a matrix using calc groups?

[v-luwang-msft]

Hi @epresson ，

Can you share the pbix files for both solutions?Remember to remove confident data.

Best Regards

Lucien