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