Calculation Groups help

[mp390988]

Hi,

Does anyone know how I can create the below visual using calculation groups?
As you can see. the column headers has 2 tiers and needs to be exactly like this.

I tried using calculation groups but got stuck at this point.

When I drill down to the lowest level of data in my matrix visual I get this which is wrong.

Thank You

[mp390988]

Hi @danextian,

What values should the calculated column contain? My column headers have two levels and the first 6 columns the headers are on just 1 level.

Thank You,

M

[danextian · Accepted solution]

Your calc columns would go like this:

Group =
VAR _'Time Intelligence'[Calculation group column] = [Column]
RETURN
    SWITCH (
        TRUE (),
        _Column IN { "MTD", "QTD", "YTD" }, "",
        _Column IN { "MTD Target", "QTD Target", "YTD Target" }, " ",
        _Column IN { "£", "%" }, "MTD VAR",
        _Column IN { "£.", "% " }, "QTD VAR",
        _Column IN { "£..", "%  " }, "YTD VAR",
        _Column = "Rate", "Daily Run",
        _Column = "Rate Target", "Daily Run ",
        _Column IN { "£...", "%   " }, "MTD RR VAR"
    )


Sort =
VAR _'Time Intelligence'[Calculation group column] = [Column]
RETURN
    SWITCH (
        TRUE (),
        _Column IN { "MTD", "QTD", "YTD" }, 1,
        _Column IN { "MTD Target", "QTD Target", "YTD Target" }, 2,
        _Column IN { "£", "%" }, 3,
        _Column IN { "£.", "% " }, 4,
        _Column IN { "£..", "%  " }, 5,
        _Column = "Rate", 6,
        _Column = "Rate Target", 7,
        _Column IN { "£...", "%   " }, 8
    )

A custom sort column is needed so the groups follow the same order as shown in the screenshot. Notice the trailing spaces in the grouping values - these are intentional so Power BI treats them as different values, allowing them to appear as separate columns.

Also note the periods. Power BI does not allow multiple calculation items with identical names. A workaround is to replace the period with a zero-width space character, which can be copied from [Unicode Explorer - Zero Width Space](https://unicode-explorer.com/c/200B?utm_source=chatgpt.com).

Since the character is invisible, it can be difficult to work with. After replacing the periods, both calc columns will also need to be updated accordingly.

You should be able to view the calculation group table in data view

Also, the calcuation items can be edited in the model/diagram view

[mp390988]

Hi @danextian ,

Thank you so much for your dedicated time in explaining the solution. Much appreciated 🙂
Your explanation helped me achieve what I wated.

Thank You,
M

[OwenAuger]

Hi @mp390988 

If you need to have a two-level hierarchy containing your calculation items to be applied across columns, one solution is to add a calculated column to your calculation group table representing the top level of the hierarchy.

You would then place the top level column and the calculation item column in the Matrix visual columns.

See this blog post for an example.

Since your first 6 columns (MTD to YTD Target) have only a single level, you would have to have a "blank" top level for those items to create a similar appearance.

Would something like this work for you?

[mp390988]

Hi @OwenAuger ,

Thank you for your response.

I read your post a few times to see if I can accomplish what I need here. You are right that I need a 2 level hierarchy in my column headers for my matrix and the first 6 columns only have a single level. 

But I am still confused on how to do it even after reading your post a few times and am going round and round in circles. 

I guess this is quite a specific and pretty complex task which is why I am finding it so difficult. 

Thank You,

M

[danextian]

Find the calculation group  column in the fields pane and then create a calculated column in the same table