Addcolumn -dax

[eitanhcs]

Hi,
i would like to start using the Summarize and ADD Column formulas.
i managed to create to use the formula that will create new virtual columns for me but i dont understand how to use them

for example: i have created the below formula that create a virtual table with 8 columns ( Sales Current Year,Sales Last Year,Qty Current Year,Qty LY,Current Avg Price,LY Avg Price,Qty_Diff,Price_Diff)
now i want,for example, to present in a table the Sum of the virtual column :Price_Diff
how can i use this column?
thanks


------------------------------------------------------------------

Z-TTest Summerize = var Summmarize_ByItem= SUMMARIZE('IT-Sales Transactions','IT-Sales Transactions'[Item Num])
var CurrentAvgPrice=[Sales Current Year]/[Sales Qty Current Year]
var LastAvgPrice=[Sales Last Year]/[Sales Qty Last Year]
var QtyDiff=[Sales Qty Current Year]-[Sales Qty Last Year]


return

ADDCOLUMNS(Summmarize_ByItem,
"Sales Current Year",[Sales Current Year],
"Sales Last Year",[Sales Last Year],
"Qty Current Year",[Sales Qty Current Year],
"Qty LY",[Sales Qty Last Year],
"Current Avg Price",IF([Sales Qty Current Year]=BLANK(),BLANK(),[Sales Current Year]/[Sales Qty Current Year]),
"LY Avg Price",IF([Sales Qty Last Year]=BLANK(),BLANK(),[Sales Last Year]/[Sales Qty Last Year]),
"Qty_Diff",IF([Sales Qty Current Year]=BLANK() || [Sales Qty Last Year]=BLANK(),[Sales Current Year]-[Sales Last Year],([Sales Qty Current Year]-[Sales Qty Last Year])*([Sales Last Year]/[Sales Qty Last Year])),
"Price_Diff",IF([Sales Qty Current Year]=BLANK() || [Sales Qty Last Year]=BLANK(),0,([Sales Current Year]/[Sales Qty Current Year]-[Sales Last Year]/[Sales Qty Last Year])*[Sales Qty Current Year]),"Qty Var",1)

 

[Matt Allington]

ADDCOLUMNS is a DAX Query function - it is designed to be used at run time when creating virtual tables. You can't materialise these tables in Excel - you can only do that in a query tool like DAX studio or SSMS (or even New Table in Power BI). It is not wrong to do it they way you are doing it, but I do wonder why you are taking this approach vs a more traditional BI approach. Eg if you did this in Excel, you could write all these measures, create a pivot table with your items on the rows and add your measures to the pivot table.

 

[eitanhcs]

Thanks,
i mainly tried it this way to learn these functions, i have created the new table in Power BI "New Table" and made sure it looks the way i think it should look

what i am trying to do is to create a var formula which is basically a calculation of (Q1-Q0)*P0 for every line(summarized to the item level) and then sum them up to have the total Quantity Var
i thought that add column can help me do it in a measure without relaying on a physical table.

if this not the proper use of this formula, in which cases it is recommended to use ADDCOLUMNS?
thanks

 

[Matt Allington]

I can't really say if it is proper use or not, just that it is not how I would normally use it. Also it is very difficult to read code like yours without the context of what you are doing. I normally do such work myself live in Dax studio building one piece of the puzzle at a time. If I ever have to debug, I normally have to re-start the process in DAX studio to work out what I did. For this reason it is very hard for me to comment on your code.

Also FYI

Regarding this line...

"Current Avg Price",IF([Sales Qty Current Year]=BLANK(),BLANK(),[Sales Current Year]/[Sales Qty Current Year]),

is identical to this line

"Current Avg Price",DIVIDE([Sales Current Year],[Sales Qty Current Year]),

This is exactly what the DIVIDE function does - checks for divide by zero errors

Same applies to the other divide rows in your code

 

[eitanhcs]

Thanks