Add summarizing column and reference column

[Nipius]

Hi all!

I'm trying to replicate an Excel dashboard in Power BI. One of the issues I face is how to add two particular columns to my Matrix visual. In the below picture these columns are 2+ (sum of the values in columns 6 to 2) and INT.

I currently have the following in Power BI:

Snapshot           2018M12                                                            2018M04

Group                WL1     WL2     WL3     WL4     WL5     Total       WL1    WL2  et cetera

Group 1             10        6           3          2          1           22          11       6

Group 2             34        20         8          3          0           65

My intention is to add a column combining WL2, WL3, WL4 and WL5 into WL2+. Right now, I've done this by adding a custom Excel file where I've marked these WL's as part of the WL2+ bucket, added this to the data model and made a connection.

However, as a consequence, it then shows only the WL1 and WL2+ columns. Where I'd ideally would like to keep the WL2 - WL5 a well and then show the WL2+ as a reference column.

Next to this, I'd also like to add a another column in between, showing a specific group part of the WL1 group (in the picture above the INT column). This is a group of people part of the WL1 group, but part of the "Interns" group in the "Employee Type" field.

Hope this all makes sense, as my mindset most likely is still extremely oriented on Excel.

Thanks for the support 🙂

[v-juanli-msft · Accepted solution]

Hi @Nipius '

I create a new table

Table 3 =
UNION (
    SUMMARIZE (
        Sheet4,
        Sheet4[year/month],
        Sheet4[group],
        Sheet4[value],
        Sheet4[w]
    ),
    SELECTCOLUMNS (
        SUMMARIZE (
            ADDCOLUMNS (
                Sheet4,
                "number", VALUE ( RIGHT ( [w], 1 ) ),
                "2+", CALCULATE (
                    SUM ( Sheet4[value] ),
                    FILTER (
                        ALLEXCEPT ( Sheet4, Sheet4[year/month], Sheet4[group] ),
                        VALUE ( RIGHT ( [w], 1 ) ) >= 2
                    )
                ),
                "w+", "2+"
            ),
            Sheet4[year/month],
            Sheet4[group],
            [2+],
            [w+]
        ),
        "year/month", [year/month],
        "group", [group],
        "value", [2+],
        "w", [w+]
    )
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nipius]

Apologies for the delayed response! Really appreciate your suolution!