Add summarizing column and reference column

Question

Hi all!&nbsp;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.&nbsp;&nbsp;&nbsp;I currently have the following in Power BI:&nbsp;Snapshot&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2018M12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2018M04Group&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WL1&nbsp; &nbsp; &nbsp;WL2&nbsp; &nbsp; &nbsp;WL3&nbsp; &nbsp; &nbsp;WL4&nbsp; &nbsp; &nbsp;WL5&nbsp; &nbsp; &nbsp;Total&nbsp; &nbsp; &nbsp; &nbsp;WL1&nbsp; &nbsp; WL2&nbsp; et ceteraGroup 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; 6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;22&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11&nbsp; &nbsp; &nbsp; &nbsp;6Group 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;34&nbsp; &nbsp; &nbsp; &nbsp; 20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;65&nbsp;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.&nbsp;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.&nbsp;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.&nbsp;Hope this all makes sense, as my mindset most likely is still extremely oriented on Excel.&nbsp;Thanks for the support :)

v-juanli-msft · Accepted Answer

Hi&nbsp;Nipius&nbsp;'
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 ) ) &gt;= 2
                    )
                ),
                "w+", "2+"
            ),
            Sheet4[year/month],
            Sheet4[group],
            [2+],
            [w+]
        ),
        "year/month", [year/month],
        "group", [group],
        "value", [2+],
        "w", [w+]
    )
)

&nbsp;
&nbsp;
Best RegardsMaggie
&nbsp;
Community Support Team _ Maggie LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Add summarizing column and reference column

2 Replies

Related content

Parameter in summarize

Summarize Multiple Columns

UDF with variable table reference and fixed column reference

Don't Summarize....still summarizes...?

DAXUDF Reference a column