Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
1 year ago
Solved

Add Measure as a Column Subtotal in a Matrix

Hello

I'm doing a shift quadrant, and I'd like to know if it's possible to add a measure as a column subtotal.

In the following image I have the quadrant and in the blank matrix, the measurements I want to add appear:

If it is not possible to have an idea to show them and if they order, they should be ordered both at the same time?

  • danextian's avatar
    danextian
    1 year ago

    Hi Syndicate_Admin 

     

    First, add a day column to your fact table. Then create this disconnected calculated table.

    CombinedDimensions = 
VAR _day =
    DISTINCT (
        SELECTCOLUMNS (
            Cuadrante,
            "Day", FORMAT ( Cuadrante[Dia], "#,#" ),
            "Sort", Cuadrante[Dia]
        )
    )
VAR tvb =
    DATATABLE (
        "Day", STRING,
        "Sort", INTEGER,
        {
            { "T", 51 },
            { "V", 52 },
            { "B", 53 }
        }
    )
RETURN
    UNION ( _day, tvb )

    Dont forget to apply custom sort.

    Create this measure to bind your fact table and this disconnected table in a viz.

    CombinedValue = 
IF (
    SELECTEDVALUE ( CombinedDimensions[Sort] ) <= 31,
    CALCULATE (
        MIN ( Cuadrante[EVENTO] ),
        KEEPFILTERS ( TREATAS ( VALUES ( CombinedDimensions[Sort] ), Cuadrante[Dia] ) )
    ),
    SWITCH (
        SELECTEDVALUE ( CombinedDimensions[Day] ),
        "T", FORMAT ( [Total_mañanas], "#,0" ),
        "V", FORMAT ( [Total_Vacaciones], "#,0" ),
        "B", FORMAT ( [Total_baja], "#,0" )
    )
)

    And the result is

    Reapply conditional formatting.

     

    Please see attached pbix

6 Replies

  • saud968's avatar
    saud968
    Memorable Member
    1 year ago

    From the provided image, it seems you want to add the following measures as column subtotals:

    TVB
    ID
    M
    D
    Adding Measures as Column Subtotals:

    Unfortunately, Power BI does not directly support adding measures as column subtotals. However, we can achieve a similar effect using calculated measures and a matrix visual.

    Steps:

    Create Calculated Measures:

    For each measure you want to subtotal, create a calculated measure that calculates the sum for the current row. For example:


    TVB Subtotal = SUM(Table[TVB])
    ID Subtotal = SUM(Table[ID])
    M Subtotal = SUM(Table[M])
    D Subtotal = SUM(Table[D])
    Create a Matrix Visual:

    Place the following fields in the matrix visual:

    Rows: ID
    Columns: 1, 2, 3, ... (your shift columns)
    Values: TVB Subtotal, ID Subtotal, M Subtotal, D Subtotal
    Format the Matrix:

    Customize the matrix to display the subtotals in the desired format. You can adjust the font size, color, and alignment of the subtotal values.
    Considerations:

    Data Model: Ensure your data model is structured correctly. The measures should be based on the appropriate tables and columns.
    Filter Context: Be mindful of the filter context in your matrix. If you have slicers or other filters, the subtotals will be calculated based on the filtered data.
    Measure Placement: You can adjust the placement of the subtotal measures within the matrix to create a clear and intuitive layout.

    Alternative Approach (Conditional Formatting):

    If you want to highlight the subtotals without adding them as separate columns, you can use conditional formatting to apply different formatting to the subtotal rows. This can be done based on a specific condition, such as the row containing the subtotal values.

    Best Regards
    Saud Ansari
    If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!


  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Syndicate_Admin.

    You mean after column 31, you want to add T, V and B? Can you please post a workable sample data (not an image) or a sanitized copy of your pbix?

      • danextian's avatar
        danextian
        Super User
        1 year ago

        Hi Syndicate_Admin 

         

        First, add a day column to your fact table. Then create this disconnected calculated table.

        CombinedDimensions = 
VAR _day =
    DISTINCT (
        SELECTCOLUMNS (
            Cuadrante,
            "Day", FORMAT ( Cuadrante[Dia], "#,#" ),
            "Sort", Cuadrante[Dia]
        )
    )
VAR tvb =
    DATATABLE (
        "Day", STRING,
        "Sort", INTEGER,
        {
            { "T", 51 },
            { "V", 52 },
            { "B", 53 }
        }
    )
RETURN
    UNION ( _day, tvb )

        Dont forget to apply custom sort.

        Create this measure to bind your fact table and this disconnected table in a viz.

        CombinedValue = 
IF (
    SELECTEDVALUE ( CombinedDimensions[Sort] ) <= 31,
    CALCULATE (
        MIN ( Cuadrante[EVENTO] ),
        KEEPFILTERS ( TREATAS ( VALUES ( CombinedDimensions[Sort] ), Cuadrante[Dia] ) )
    ),
    SWITCH (
        SELECTEDVALUE ( CombinedDimensions[Day] ),
        "T", FORMAT ( [Total_mañanas], "#,0" ),
        "V", FORMAT ( [Total_Vacaciones], "#,0" ),
        "B", FORMAT ( [Total_baja], "#,0" )
    )
)

        And the result is

        Reapply conditional formatting.

         

        Please see attached pbix