How to use ADDCOLUMNS and SUMMARIZE Functions to create a Summary table from Related Tables in Power BI

How to create a Summary table with Group By columns from Related Tables in Power BI

Scenario:

We wants to create a Sales Summary using the facts from “tbl_Sales” and dimensions(group by columns) from the related tables, tbl_Calendar, tbl_Products from a model like below:

Power BI Model:

Now lets have a look at the data for the fact table, tbl_Sales, from which we need to create the summary table.

As per our Scenario, lets create a Summary table based on the fact table “tbl_Sales”, and the dimension tables , “tbl_Calendar” and “tbl_Products”.

Method 1: Using ADDCOLUMNS and SUMMARIZE (without using variables):

Sales_Summary =

ADDCOLUMNS(SUMMARIZE(ADDCOLUMNS(tbl_Sales,

“QTR_Year”, RELATED(tbl_Calendar[QTR_Year]),

“Prod_Name”, RELATED(tbl_Products[Prod_Name])),

[QTR_Year],[Prod_Name],

“Total_Sales”, SUM(tbl_Sales[Gross_Sales]),

“Discount”,SUM(tbl_Sales[Gross_Sales])*0.05),

“Net_Sales”,[Total_Sales]-[Discount])

Method 2: Using ADDCOLUMNS and SUMMARIZE (with use of variables):

We can re-write the above logic using variables in more readable format.

vSales_Summary =

VAR vAddColumns1 = ADDCOLUMNS(tbl_Sales,

“QTR_Year”, RELATED(tbl_Calendar[QTR_Year]),

“Prod_Name”, RELATED(tbl_Products[Prod_Name]))

VAR vSummrize1 = SUMMARIZE( vAddColumns1,

[QTR_Year],[Prod_Name],

“Total_Sales”, SUM(tbl_Sales[Gross_Sales]),

“Discount”,SUM(tbl_Sales[Gross_Sales])*0.05)

VAR vAddColunns2= ADDCOLUMNS(vSummrize1, “Net_Sales”,[Total_Sales]-[Discount])

RETURN vAddColunns2

# Output:

Method 3: Using ADDCOLUMNS, FILTER, SUMMARIZE and SELECTCOLUMNS in the same DAX expression (with use of variables):

vSales_Summary =

VAR vAddCols1 = ADDCOLUMNS(tbl_Sales, “FY_Year”, RELATED(tbl_Calendar[Year]),

“QTR_Year”, RELATED(tbl_Calendar[QTR_Year]),

“Prod_Name”, RELATED(tbl_Products[Prod_Name]))

VAR vFilterCol1= FILTER(vAddCols1, [QTR_Year] IN {“Q1-2014″,”Q1-2015″,”Q1-2016”})

VAR vSummary1 = SUMMARIZE( vFilterCol1,

[FY_Year],[QTR_Year],[Prod_Name],

“Total_Sales”, SUM(tbl_Sales[Gross_Sales]),

“Discount”,SUM(tbl_Sales[Gross_Sales])*0.05)

RETURN

SELECTCOLUMNS(vSummary1,”FY_QTR”,[QTR_Year],”Prod_Name”,[Prod_Name],

“NetSales”,[Total_Sales]-[Discount])

# Output:

Notes :

The ADDCOLUMNS is a Table valued Function(returns table), that keeps all the existing columns in the base table and adds more columns to that based on the expression.

On the other hand, SLELECTCOLUMNS is also a Table valued Function but it starts with no columns from the base table, and builds a custom subset of columns or with additional calculated columns on it.

# ———————————————————————————————————————— #

Notes:

The above DAX expression can DEFINE and EVALUATE in the following manner in the DAX Studio :

DEFINE

    VAR vAddCols1 =

        ADDCOLUMNS (

            tbl_Sales,

            “FY_Year”, RELATED ( tbl_Calendar[Year] ),

            “QTR_Year”, RELATED ( tbl_Calendar[QTR_Year] ),

            “Prod_Name”, RELATED ( tbl_Products[Prod_Name] )

        )

    VAR vFilterCol1 =

        FILTER ( vAddCols1, [QTR_Year] IN { “Q1-2014”, “Q1-2015”, “Q1-2016” } )

    VAR vSummary1 =

        SUMMARIZE (

            vFilterCol1,

            [FY_Year],

            [QTR_Year],

            [Prod_Name],

            “Total_Sales”, SUM ( tbl_Sales[Gross_Sales] ),

            “Discount”, SUM ( tbl_Sales[Gross_Sales] ) * 0.05

        )

EVALUATE
SELECTCOLUMNS (

    vSummary1,

    “FY_QTR”, [QTR_Year],

    “Prod_Name”, [Prod_Name],

    “NetSales”, [Total_Sales] – [Discount]
)

——————————————————————————————————–

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

——————————————————————————————————–

Source link

How to use ADDCOLUMNS and SUMMARIZE Functions to create a Summary table from Related Tables in Power BI | Power BI Blog

Be the first to comment

Leave a Reply Cancel reply