topic Add a "Total" value in a table with product categories in DAX Commands and Tips

Add a "Total" value in a table with product categories

MakZH — Tue, 11 Jul 2023 09:26:08 GMT

Dear community

I am looking for a way to get a table with the total sales of the product categories and a "Total" row with the sum of all the total sales of each product categories (see the prefered outcome below > Table 1)

Table 1: Prefered outcome

Currently the table contains only the product categories (see the screenshot below > Table 2)

Table 2: Current table

To calculate the total I intend to create a new table with the following DAX script, which generates unfortunately not the expected outcome:

CategoriesWithTotals = --This script creates a table with the product categories from the Product Category VAR _ProductCategories = DISTINCT( UNION( SUMMARIZE('Test Data (3)', 'Test Data (3)'[Product Category]), DATATABLE("Product Category", STRING, {{"Total"}} --Create a table with the data type "STRING" ) ) ) VAR _TotalSum = SUMX('Test Data (3)',[Total]) VAR _SetTotal = SWITCH( TRUE(), _ProductCategories <> "Total", 123, _ProductCategories = "Total", SUMX('Test Data (3)',[Total]), BLANK() ) Return ADDCOLUMNS( _ProductCategories, "Total", _TotalSum )

Thank you for your support.

Re: Add a "Total" value in a table with product categories

Anonymous — Thu, 13 Jul 2023 02:47:52 GMT

Hi MakZH ,

Please try this:

Table2 = VAR _ProductCategories = UNION ( SELECTCOLUMNS ( 'Table1', "Product Category", 'Table1'[Product Category] ), ROW ( "Product Category", "Total" ) ) RETURN ADDCOLUMNS ( _ProductCategories, "Sum Product Category", IF ( [Product Category] = "Total", SUM ( 'Table1'[Sum Product Category] ), CALCULATE ( SUM ( 'Table1'[Sum Product Category] ), 'Table1'[Product Category] = EARLIER ( [Product Category] ) ) ) )

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum

Re: Add a "Total" value in a table with product categories

MakZH — Thu, 13 Jul 2023 07:36:53 GMT

Thank you Gao, this works perfectly 🙂

I also had a similar approach, first to create a calculated table with the following script

CategoriesWithTotals = --This script creates a table with the product categories from the Product Category DISTINCT( UNION( SUMMARIZE('Test Data (3)', 'Test Data (3)'[Product Category]), DATATABLE("Product Category", STRING, {{"Total"}} --Create a table with the data type "STRING" ) ) )

and then create a measure for the totals

Average Selling Price incl Total = Var Total = CALCULATE(SUMX('Test Data (3)', [Total]), ALL(CategoriesWithTotals[Product Category])) RETURN IF ( SELECTEDVALUE(CategoriesWithTotals[Product Category])="Total", Total, SUMX('Test Data (3)', [Total]) )