Monday, January 31, 2022

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

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog