Add Measure as a Column Subtotal in a Matrix

[Syndicate_Admin]

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]

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?

[Syndicate_Admin]

Hello.

What I want is that after the last day of the month these three calculated measures (T, V and B) appear. or some idea to visualize the two matrices in the same order and if one is ordered, the other is ordered.

I add a link in drive to share the file:

https://drive.google.com/file/d/1n5E3vDVFO2VUz640JsPX0KBhHRn9fk75/view?usp=sharing

[danextian · Accepted solution]

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

[Syndicate_Admin]

Perfect, this is just what I needed. I have analyzed the process and it is a great development.

Thanks a lot.

[saud968]

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!

[Syndicate_Admin]

Hi Saud.

I don't understand what you explain to me, I have put the table in the following link, in case you can show it to me:
https://drive.google.com/file/d/1n5E3vDVFO2VUz640JsPX0KBhHRn9fk75/view?usp=sharing

Greetings and thank you.