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

[MakZH]

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. 

[v-cgao-msft · Accepted solution]

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

[v-cgao-msft · Accepted solution]

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

[MakZH · Accepted solution]

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