Forum Discussion

mp390988's avatar
mp390988
Post Partisan
3 months ago
Solved

Calculation Groups help

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 calc...
Need Help
Show and Tell
  • danextian's avatar
    danextian
    3 months ago

    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

     

     

OwenAuger's avatar
OwenAuger
Super User
3 months ago

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's avatar
    mp390988
    Post Partisan
    3 months ago

    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's avatar
      danextian
      Super User
      3 months ago

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