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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
AD_SNOW
Helper I
Helper I

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

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

CategoryJan-22Feb-22Mar-22Total
Pens123234435792
Scale34556754966
Ink Bottle76557671348
Apple234545623455146
Orange2344556335
Graps345278425
Banana345566451046
Sofa234526764575478
Table34454450938
Fan5785672341379

 

I want the output as below screenshot

Expected outputExpected output

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

Thanks!

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

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:

ftable.jpg

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

date table.jpg

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

 

cat table.jpg

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

 

matrix rows.jpg

Set up the model as follows:

model.jpg

 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:

result.jpg

Sample PBIX file attached

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

6 REPLIES 6
PaulDBrown
Community Champion
Community Champion

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:

ftable.jpg

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

date table.jpg

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

 

cat table.jpg

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

 

matrix rows.jpg

Set up the model as follows:

model.jpg

 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:

result.jpg

Sample PBIX file attached

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






PaulDBrown
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

Hi @AD_SNOW ,

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

 

Best regards,
Community Support Team_ Binbin Yu

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
Not applicable

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

vbinbinyumsft_0-1669714910334.png

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.

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!