Matrix Visualization - Create Custom Total Column

[jerryr125]

Hi - 
I would create a custom total column to appear at the end of my matrix visulalization.  

Data from Data Source

Product Name	Category	Amount
Ice Cream	StartingInv	100
Ice Cream	SoldInv	25
Ice Cream	DefectInv	5
Cake	StartingInv	200
Cake	SoldInv	50
Cake	DefectInv	10

Example Matrix Visualization:

Product Name	StartingInv	SoldInv	DefectInv	FinalInv
Ice Cream	100	25	5	70
Cake	200	50	10	140

The Matrix Visualization is displaying:

Rows: Product Name

Columns: Category

Value: SUM of Amount

I would like to add the FinalInv column which is calculate by:

StatringInv - SoldInv - DefectInv

Any thoughts ? Thanks - Jerryr

[jerryr125]

Hi   @rajendraongole1  and thank you very much !

How do you set the visualization so that the FinalInv column appears at the end of matrix visualization (see image below).  Any help is appreciated! Thanks - Jerry

[rajendraongole1]

Hi @jerryr125  -  Please follow the below steps.

Steps to Move FinalInv to the End
Reorder Columns in the Values Section:

Open the Fields pane on the right side of the Power BI interface.
Under your Matrix visualization:
Ensure that FinalInv is listed last in the Values section.
Drag and drop the FinalInv field below all the other fields in the Values section.
Remove Unwanted Subtotals (Optional):

If the subtotals for FinalInv are still appearing in every category, disable them:
Select your Matrix visualization.
Go to the Format Visual pane on the right.
Navigate to the Subtotals section.
Turn off Column subtotals or configure subtotals to exclude FinalInv.
Check Column Layout:

Ensure that the Category field is still in the Columns section in your Matrix visualization.
The measure FinalInv should remain only in the Values section.

[rajendraongole1]

Hi @jerryr125  - Please check the attached pbix file.

[jerryr125]

Hi   @rajendraongole1  and thank you very much !

How do you set the visualization so that the FinalInv column appears at the end of matrix visualization (see image below).  Any help is appreciated! Thanks - Jerry

[Anonymous · Accepted solution]

Thanks for the reply from rajendraongole1, please allow me to provide another insight:

Hi, @jerryr125 
Thanks for reaching out to the Microsoft fabric community forum.

Regarding the issue you raised, my solution is as follows:

You can use the UNION() function combined with an index column for sorting:

1.First, create a calculated table:

Table 2 = 
VAR f2 = ADDCOLUMNS(
        'Table',
        "index", CALCULATE(
            COUNTROWS('Table'),
            ALLEXCEPT(
                'Table',
                'Table'[Product Name]
            ),
            'Table'[Category] >= EARLIER('Table'[Category])
        )
    )
    VAR f1 = SUMMARIZE(
        'Table',
        'Table'[Product Name],
        "Category", "FinalInv",
        "Amount", VAR c1 = CALCULATE(
            SUM('Table'[Amount]),
            ALLEXCEPT(
                'Table',
                'Table'[Product Name]
            ),
            'Table'[Category] = "StartingInv"
        )
        VAR c2 = CALCULATE(
            SUM('Table'[Amount]),
            ALLEXCEPT(
                'Table',
                'Table'[Product Name]
            ),
            'Table'[Category] = "SoldInv"
        )
        VAR c3 = CALCULATE(
            SUM('Table'[Amount]),
            ALLEXCEPT(
                'Table',
                'Table'[Product Name]
            ),
            'Table'[Category] = "DefectInv"
        )
        RETURN
            c1 - c2 - c3,
        "index", 4
    )
    RETURN
        UNION(
            f2,
            f1
        )

2.Next, sort based on the index column:

3.Here's my final result, which I hope meets your requirements.

4.For more details, please refer to:

How to put the row and column subtotals in front o... - Microsoft Fabric Community

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[polymathy2017]

The goal of a forum like this is to educate users, not show how smart or creative you are in engineering solutions that a poster won't comprehend.

C'mon, man, read the room. He's clearly new to Power BI and doesn't understand it hardly at all, so you think the best solution is a very long DAX expression that includes table joins that he's surely not going to understand? Gotta put your thinking cap on before wasting bandwidth on a complicated solution.

The simplest way for you to achieve what you want is to pivot the category column in Power Query to create separate columns for starting inventory, sold inventory, and defective inventory and then run your aggregations across each column.

Of course, since he doesn't know how to arrive at an aggregated column total, even this simple approach may not be simple. He needs to learn some basic Power BI/DAX best practices. Sounds like he's trying to create something without knowing anything about the tool he's working with.