add extra column to existing dax query

[aimamasandpapas]

Hi I have this existing DAX query that I need to alter to add an additional query to summarize by

I'm just looking to add 'Calendar'[FiscalPeriod] but seem to be struggling with it

DEFINE     
MEASURE RetailSales[VAT_RATE] =         DIVIDE ( [NB VAT], [NB Nett Sales] )     
MEASURE RetailSales[RETAIL_VALUE] = [NB Nett Sales] + [NB VAT]     
MEASURE RetailSales[RETAIL_PRICE] =         DIVIDE ( [NB Gross Sales], [NB Qty] ) 
EVALUATE 
GENERATE (     
CALCULATETABLE (         
SUMMARIZE (             
'Item',             
'Item'[CategoryCode],             
'Item'[Category],             
'Item'[GroupCode],             
'Item'[Group],             
'Item'[SubGroupCode],             
'Item'[SubGroup],             
'Item'[ProductCode],             
'Item'[Product],             
'Item'[ItemCode],             
'Item'[Barcode],             
'Item'[Description]         ),         
'Item'[LastTransactionDateId] >= 20150104     
),     
FILTER (         
ADDCOLUMNS (             
CALCULATETABLE (                 
VALUES ( 'Store'[Name] ),        
'Store'[CountryName] = "United Kingdom", 
'Store'[SubLevel] = "Ecommerce" 
|| 'Store'[SubLevel] =  "OOT" 
|| 'Store'[SubLevel] =  "Mall" 
|| 'Store'[SubLevel] =  "Concession" 
|| 'Store'[SubLevel] =  "Telephone" )

,  "Retail Units Sold"  , 
CALCULATE (                 [NB Qty],                 
'Calendar'[DateKey] >= 20220206,                 
'Calendar'[DateKey] <= 20220430            
),  "Retail Value"  , 
CALCULATE (                 
RetailSales[RETAIL_VALUE],                 
'Calendar'[DateKey] >=20220206,                 
'Calendar'[DateKey] <= 20220430            
)*100                
,   "Retail Price"  , 
CALCULATE (                 
RetailSales[RETAIL_PRICE],                 
'Calendar'[DateKey] >= 20220206,                 
'Calendar'[DateKey] <= 20220430             
)*100                 
, "vat"  , 
CALCULATE (                 
[NB VAT],                 
'Calendar'[DateKey] >= 20220206,                 
'Calendar'[DateKey] <= 20220430             
)*100,  "Tax Rate" , ROUND (                 
CALCULATE (                     
RetailSales[VAT_RATE],                     
'Calendar'[DateKey] >= 20220206,                 
'Calendar'[DateKey] <= 20220430                 
)                     * 100,                
0             
)         
),         
CALCULATE (             
[NB Qty],             
'Calendar'[DateKey] >= 20220206,                 
'Calendar'[DateKey] <= 20220430        
)            
<> 0     
) 
)

 regards

[johnt75 · Accepted solution]

You could use SUMMARIZECOLUMNS instead of SUMMARIZE. That would allow you to include both 'Store'[Name] and 'Calendar'[FiscalPeriod] in the main list of columns to summarize by, and you wouldn't need the generate

[aimamasandpapas]

Great thanks - the original wasn't mine and it looked overly complicated - this is new code which gives me the same result

DEFINE
MEASURE RetailSales[VAT_RATE] = DIVIDE ( [NB VAT], [NB Nett Sales] )
MEASURE RetailSales[RETAIL_VALUE] = [NB Nett Sales] + [NB VAT]
MEASURE RetailSales[RETAIL_PRICE] = DIVIDE ( [NB Gross Sales], [NB Qty] )

EVALUATE
FILTER(
SUMMARIZECOLUMNS(

'Item'[CategoryCode],
'Item'[Category],
'Item'[GroupCode],
'Item'[Group],
'Item'[SubGroupCode],
'Item'[SubGroup],
'Item'[ProductCode],
'Item'[Product],
'Item'[ItemCode],
'Item'[Barcode],
'Item'[Description],
'Store'[Name],
CALCULATETABLE ('Store',
'Store'[CountryName] = "United Kingdom",
'Store'[SubLevel] = "Ecommerce"
|| 'Store'[SubLevel] = "OOT"
|| 'Store'[SubLevel] = "Mall"
|| 'Store'[SubLevel] = "Concession"
|| 'Store'[SubLevel] = "Telephone" ),
CALCULATETABLE('Item','Item'[LastTransactionDateId] >= 20150104),
"Retail Units Sold" ,
CALCULATE ( [NB Qty],
'Calendar'[DateKey] >= 20220206,
'Calendar'[DateKey] <= 20220430
), "Retail Value" ,
CALCULATE (
RetailSales[RETAIL_VALUE],
'Calendar'[DateKey] >=20220206,
'Calendar'[DateKey] <= 20220430
)*100
, "Retail Price" ,
CALCULATE (
RetailSales[RETAIL_PRICE],
'Calendar'[DateKey] >= 20220206,
'Calendar'[DateKey] <= 20220430
)*100
, "vat" ,
CALCULATE (
[NB VAT],
'Calendar'[DateKey] >= 20220206,
'Calendar'[DateKey] <= 20220430
)*100, "Tax Rate" ,ROUND (
CALCULATE (
RetailSales[VAT_RATE],
'Calendar'[DateKey] >= 20220206,
'Calendar'[DateKey] <= 20220430
) * 100,
0
)
),
CALCULATE (
[NB Qty],
'Calendar'[DateKey] >= 20220206,
'Calendar'[DateKey] <= 20220430
)
<> 0
)