Create custom total row and percentage values in between the rows in table

[AD_SNOW]

Team, I have a requirement as I need to show custom total row and percentage values in between the actual data table.

This is how the actual data looks like

Category	Jan-22	Feb-22	Mar-22	Total
Pens	123	234	435	792
Scale	345	567	54	966
Ink Bottle	765	576	7	1348
Apple	2345	456	2345	5146
Orange	234	45	56	335
Graps	345	2	78	425
Banana	345	56	645	1046
Sofa	2345	2676	457	5478
Table	34	454	450	938
Fan	578	567	234	1379

I want the output as below screenshot

Expected output

Please let me know if you are not clear with the requirement. Kindly help me with the same.

Thanks!

[PaulDBrown · Accepted solution]

Here is one way...but before we dig in, two important considerations:
1) This example is based on a very small dataset. There may well be an important hit on performance once applied to a substantial number of rows

2) It will be a "nightmare" to maintain if more "special categories" are added

To start off, the main fact table ('fTable') is loaded like this: the period columns are unpivoted and converted into a date field (to be able to use a Date table). The transformations of course done in Power Query:

I've also created the 'Period Table' (Date Table) in Power Query as a dimension.

Create a dimension table for category with the following DAX in a new table:

Category Table =
VAR _Stucture =
    ADDCOLUMNS (
        DISTINCT ( fTable[Category] ),
        "Group Category",
            SWITCH (
                TRUE (),
                fTable[Category] IN { "Pens", "Scale", "Ink Bottle" }, "Stationary",
                fTable[Category] IN { "Sofa", "Table", "Fan" }, "Furniture",
                fTable[Category] IN { "Shirts", "Pants" }, "Clothing",
                "Fruits"
            )
    )
VAR _GO =
    ADDCOLUMNS (
        _Stucture,
        "Group Order",
            SWITCH (
                [Group Category],
                "Stationary", 1,
                "Fruits", 2,
                "Furniture", 3,
                "Clothing", 4
            )
    )
VAR _Final =
    ADDCOLUMNS (
        _GO,
        "Order",
            [Group Order] * 10000
                + RANKX ( VALUES ( fTable[Category] ), fTable[Category],, ASC )
    )
RETURN
    _Final

You now need a new table with the row structure you need for the matrix visual. I created it using this code:

Matrix Rows =
VAR _ST =
    CALCULATE (
        MAX ( 'Category Table'[Order] ),
        FILTER ( 'Category Table', 'Category Table'[Group Category] = "Stationary" )
    )
VAR _FRT =
    CALCULATE (
        MAX ( 'Category Table'[Order] ),
        FILTER ( 'Category Table', 'Category Table'[Group Category] = "Fruits" )
    )
VAR _FNT =
    CALCULATE (
        MAX ( 'Category Table'[Order] ),
        FILTER ( 'Category Table', 'Category Table'[Group Category] = "Furniture" )
    )
VAR _CT =
    CALCULATE (
        MAX ( 'Category Table'[Order] ),
        FILTER ( 'Category Table', 'Category Table'[Group Category] = "Clothing" )
    )
VAR _Sofa =
    CALCULATE (
        MAX ( 'Category Table'[Order] ),
        FILTER ( 'Category Table', 'Category Table'[Category] = "Sofa" )
    )
VAR _Table =
    CALCULATE (
        MAX ( 'Category Table'[Order] ),
        FILTER ( 'Category Table', 'Category Table'[Category] = "Table" )
    )
VAR _Fan =
    CALCULATE (
        MAX ( 'Category Table'[Order] ),
        FILTER ( 'Category Table', 'Category Table'[Category] = "Fan" )
    )
VAR _Shirts =
    CALCULATE (
        MAX ( 'Category Table'[Order] ),
        FILTER ( 'Category Table', 'Category Table'[Category] = "Shirts" )
    )
VAR _Pants =
    CALCULATE (
        MAX ( 'Category Table'[Order] ),
        FILTER ( 'Category Table', 'Category Table'[Category] = "Pants" )
    )
VAR _GT =
    MAX ( 'Category Table'[Order] ) + 10000000
VAR _Catetgories =
    SELECTCOLUMNS (
        FILTER (
            'Category Table',
            'Category Table'[Group Category] IN { "Stationary", "Fruits", "Furniture" }
        ),
        "MR", 'Category Table'[Category],
        "Order", 'Category Table'[Order]
    )
VAR _NewRows = {
    ( "Stationary Total", _ST + 10 ),
    ( "Fruits Total", _FRT + 10 ),
    ( "Furniture Total", _FNT + 10 ),
    ( "Clothing Total %", _CT + 10 ),
    ( "Sofa %", _Sofa + 0.5 ),
    ( "Table %", _Table + 0.5 ),
    ( "Fan %", _Fan + 0.5 ),
    ( "Shirts %", _Shirts + 0.5 ),
    ( "Pants %", _Pants + 0.5 ),
    ( "Grand Total", _GT )
}
VAR _TableSum =
    ADDCOLUMNS (
        UNION ( _Catetgories, _NewRows ),
        "Row Header",
            SWITCH (
                TRUE (),
                CONTAINSSTRING ( [MR], "Total" ), [MR],
                CONTAINSSTRING ( [MR], "%" ), REPT ( " ", 4 ) & [MR],
                REPT ( " ", 2 ) & [MR]
            ),
        "RHM",
            SWITCH (
                TRUE (),
                CONTAINSSTRING ( [MR], "Grand" ), "Grand Total",
                [MR] IN { "Pants %", "Shirts %", "Clothing Total %" }, "Clothing Total %",
                CONTAINSSTRING ( [MR], " %" ),
                    LOOKUPVALUE (
                        'Category Table'[Group Category],
                        'Category Table'[Order], [Order] - 0.5
                    ) & " Total",
                CONTAINSSTRING ( [MR], "Total" ), [MR],
                LOOKUPVALUE (
                    'Category Table'[Group Category],
                    'Category Table'[Category], [MR]
                ) & " Total"
            )
    )
RETURN
    ADDCOLUMNS (
        _TableSum,
        "RHM Order",
            SWITCH (
                [RHM],
                "Stationary Total", _ST,
                "Fruits Total", _FRT,
                "Furniture Total", _FNT,
                "Clothing Total %", _CT,
                "Grand Total", _GT
            )
    )

Set up the model as follows:

 With a simple SUM measure for the values, we need the following measure for the matrix:

Matrix measure =
VAR _ST =
    CALCULATE (
        [Sum Sales],
        'Category Table'[Group Category] = "Stationary",
        ALL ( 'Matrix Rows' )
    )
VAR _FRT =
    CALCULATE (
        [Sum Sales],
        'Category Table'[Group Category] = "Fruits",
        ALL ( 'Matrix Rows' )
    )
VAR _FNT =
    CALCULATE (
        [Sum Sales],
        'Category Table'[Group Category] = "Furniture",
        ALL ( 'Matrix Rows' )
    )
VAR _SofaPercent =
    DIVIDE (
        CALCULATE (
            [Sum Sales],
            FILTER ( ALL ( 'Category Table' ), 'Category Table'[Category] = "Sofa" )
        ),
        CALCULATE (
            [Sum Sales],
            DATEADD ( 'Period Table'[Date], -1, YEAR ),
            FILTER ( ALL ( 'Category Table' ), 'Category Table'[Category] = "Sofa" )
        )
    ) - 1
VAR _TablePercent =
    DIVIDE (
        CALCULATE (
            [Sum Sales],
            FILTER ( ALL ( 'Category Table' ), 'Category Table'[Category] = "Table" )
        ),
        CALCULATE (
            [Sum Sales],
            DATEADD ( 'Period Table'[Date], -1, YEAR ),
            FILTER ( ALL ( 'Category Table' ), 'Category Table'[Category] = "Table" )
        )
    ) - 1
VAR _FanPercent =
    DIVIDE (
        CALCULATE (
            [Sum Sales],
            FILTER ( ALL ( 'Category Table' ), 'Category Table'[Category] = "Fan" )
        ),
        CALCULATE (
            [Sum Sales],
            DATEADD ( 'Period Table'[Date], -1, YEAR ),
            FILTER ( ALL ( 'Category Table' ), 'Category Table'[Category] = "Fan" )
        )
    ) - 1
VAR _ShirtPercent =
    DIVIDE (
        CALCULATE (
            [Sum Sales],
            FILTER ( ALL ( 'Category Table' ), 'Category Table'[Category] = "Shirts" )
        ),
        CALCULATE (
            [Sum Sales],
            DATEADD ( 'Period Table'[Date], -1, YEAR ),
            FILTER ( ALL ( 'Category Table' ), 'Category Table'[Category] = "Shirts" )
        )
    ) - 1
VAR _PantsPercent =
    DIVIDE (
        CALCULATE (
            [Sum Sales],
            FILTER ( ALL ( 'Category Table' ), 'Category Table'[Category] = "Pants" )
        ),
        CALCULATE (
            [Sum Sales],
            DATEADD ( 'Period Table'[Date], -1, YEAR ),
            FILTER ( ALL ( 'Category Table' ), 'Category Table'[Category] = "Pants" )
        )
    ) - 1
RETURN
    IF (
        ISBLANK ( MAX ( 'Matrix Rows'[MR] ) ),
        BLANK (),
        SWITCH (
            SELECTEDVALUE ( 'Matrix Rows'[MR] ),
            "Stationary Total", _ST,
            "Fruits Total", _FRT,
            "Furniture Total", _FNT,
            "Sofa %", FORMAT ( _SofaPercent, "Percent" ),
            "Table %", FORMAT ( _TablePercent, "Percent" ),
            "Fan %", FORMAT ( _FanPercent, "Percent" ),
            "Shirts %", FORMAT ( _ShirtPercent, "Percent" ),
            "Pants %", FORMAT ( _PantsPercent, "Percent" ),
            SWITCH (
                SELECTEDVALUE ( 'Matrix Rows'[RHM] ),
                "Clothing Total %", FORMAT ( _ShirtPercent + _PantsPercent, "Percent" ),
                "Grand Total",
                    _ST + _FRT + _FNT,
                [Sum Sales]
            )
        )
    )

Now you can create the matrix using the 'Matrix Rows'[RHM] and 'Matrix Rows'[Row Header] fields as rows, the 'Period Table[dPeriod] field as columns, and the [Matrix Measure]. Hide the irrelevant columns and rows in the matrix to get:

Sample PBIX file attached

[PaulDBrown]

Did you leave out rows for "Shirts" and "Pants" from the sample data on purpose?

[Anonymous]

Hi @AD_SNOW ,

To my knowledge, your requirement could not be realized currently.

Best regards,
Community Support Team_ Binbin Yu

[akhilduvvuru]

Thanks for your response @Anonymous. Can I atleast add Pens, Scale and Ink Bottle and display the total in Stationary total?

I just tried a simple measure but the problem is, it is showing the right value if I put this measure in card visual. But If I put the same measure in a table/ matrix visual, it is showing blank. Not sure where exactly I'm doing wrong.

=if(max(Category) = "Stationary Total", (Calcualte(SUM(Jan-22), Category = "Pens")) + Calcualte(SUM(Jan-22), Category = "Scale")) + Calcualte(SUM(Jan-22), Category = "Ink Bottle"))), Jan-22)

 Please help me with the same. Thanks!

[Anonymous]

Hi @akhilduvvuru ,

Please try below steps:

1. create some measure with below dax formula

Measure =
IF (
    HASONEVALUE ( 'Table'[Category] ),
    SELECTEDVALUE ( 'Table'[Total] ),
    SUMX (
        FILTER ( ALL ( 'Table' ), [Category] IN { "Pens", "Scale", "Ink Bottle" } ),
        [Total]
    )
)

Measure2 =
IF (
    HASONEVALUE ( 'Table'[Category] ),
    SELECTEDVALUE ( 'Table'[Jan-22] ),
    SUMX (
        FILTER ( ALL ( 'Table' ), [Category] IN { "Pens", "Scale", "Ink Bottle" } ),
        [Jan-22]
    )
)

Measure3 =
IF (
    HASONEVALUE ( 'Table'[Category] ),
    SELECTEDVALUE ( 'Table'[Feb-22] ),
    SUMX (
        FILTER ( ALL ( 'Table' ), [Category] IN { "Pens", "Scale", "Ink Bottle" } ),
        [Feb-22]
    )
)

Measure4 =
IF (
    HASONEVALUE ( 'Table'[Category] ),
    SELECTEDVALUE ( 'Table'[Mar-22] ),
    SUMX (
        FILTER ( ALL ( 'Table' ), [Category] IN { "Pens", "Scale", "Ink Bottle" } ),
        [Mar-22]
    )
)

2. add a table visual with fields and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[akhilduvvuru]

@v-stephen-msft @Greg_Deckler @amitchandak would need your expertise her. Please help me with the solution. Thanks.