Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Nipius
Advocate I
Advocate I

Add summarizing column and reference column

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.

 

2019-05-24 11_15_09-FTE - Adobe Reader.png

 

 

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 🙂

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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+]
    )
)

9.png

 

 

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.

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

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+]
    )
)

9.png

 

 

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.

Apologies for the delayed response! Really appreciate your suolution!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.