fjkilken
asked on
DAX measure not calculating correctly
I'm having an issue with a DAX formula in SSAS/Tabular.
I have a calculated measure (rtn_qty_adj) which provides the values as expected, however when I reference that formula in another calculated measure (rtn_qty_avg_adj), it gives incorrect values.
It appears that "rtn_qty_avg_adj" is ignoring or filtering-out the logic of the calculated measure "rtn_qty_adj".
Basically the dataset consists of returns quantities over a (monthly) timeframe.
Where there are big peaks/excursions in the data (ie; return qty. in a month is greater than uper control limit) , then in the measure "rtn_qty_adj" I reset these peaks to the Average return qty.
So then I want to perform another averaging on the adjusted returns qtys. where the peaks are "smoothed-out" (ie; equated to the average)
Most of the calculations work just fine, except this one which is causing me a headache.
Appreciate all input/help!!
Formulae
Here's the formulae for the adjusted return qty and adjusted return qty. average
Fergal
DAX-issue.xlsx
I have a calculated measure (rtn_qty_adj) which provides the values as expected, however when I reference that formula in another calculated measure (rtn_qty_avg_adj), it gives incorrect values.
It appears that "rtn_qty_avg_adj" is ignoring or filtering-out the logic of the calculated measure "rtn_qty_adj".
Basically the dataset consists of returns quantities over a (monthly) timeframe.
Where there are big peaks/excursions in the data (ie; return qty. in a month is greater than uper control limit) , then in the measure "rtn_qty_adj" I reset these peaks to the Average return qty.
So then I want to perform another averaging on the adjusted returns qtys. where the peaks are "smoothed-out" (ie; equated to the average)
Most of the calculations work just fine, except this one which is causing me a headache.
Appreciate all input/help!!
Formulae
Here's the formulae for the adjusted return qty and adjusted return qty. average
Rtn_qty_adj:=
VAR rtn_avg =
CALCULATE(
(AVERAGEX(
SUMMARIZE( drct_excg, dates[incr_base_date], "rtn_avg", [Rtn_qty] )
,[rtn_avg] ) )
, ALL(dates))
RETURN
IF( [Rtn_qty_ucl_1sd_otlr] = 1,
IF(COUNTROWS(VALUES(dates[incr_base_date])) >1,
SUMX(VALUES(dates[incr_base_date]),
IF( [Rtn_qty_ucl_1sd_otlr] = 1, rtn_avg , [Rtn_qty] ) )
,
rtn_avg
),
[Rtn_qty]
)
Rtn_qty_avg_adj:=
CALCULATE(
AVERAGEX(
SUMMARIZE( drct_excg, dates[incr_base_date] ,"rtn_avg", [Rtn_qty_adj] )
, [rtn_avg]) )
, ALL(dates) )
Fergal
DAX-issue.xlsx
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.