PERCENTILE inside CALCULATE does not (anymore) recognise data fields

Question

Hi there,&nbsp;I recently figured out that I can write an in-memory table in DAX. I just set-up a table that spans from 1-100, and I want to add a column returning the percentile for 1-100 for a given column of another table (exchange rates).&nbsp;Now I successfully produced this using the following code.&nbsp;Table =

VAR dat = DATE(2016;1;1)

VAR pct = SUMMARIZE(
	ADDCOLUMNS(
		CALENDAR(
			dat
			;dat+99
		)
		;"pct"
		;[Date]-dat+1
	)
	;[pct]
)

VAR pct_value = ADDCOLUMNS(
	pct
	;"pct_value"
	;PERCENTILE.INC(
		'Foreign exchange rates'[INDIRECT_FX_CCY_QUOTE]
		;[pct]/100
	)
)

RETURN pct_value&nbsp;So I create this table with two columns (pct 1-100 on x-axis, pct_value as a percentile function of "pct" on y-axis).The code reads as follows:&nbsp;dat = fixed datepct = creating a one-column calculated table from 1-100 by using a dirty workaround using the calendar functionpct_value = adding one column to the pct table which should return the 1,2,3,...,100 th percentile of the field&nbsp;'Foreign exchange rates'[INDIRECT_FX_CCY_QUOTE].&nbsp;The result is valid, I double checked with excel, BUT, since the table&nbsp;'Foreign exchange rates' houses several currencies, the percentile is calculated over the whole population and therefore is meaningless.&nbsp;Since the calculated table is not related to the data model I cannot use any filters on the chart above. I therefore tried modifying the filter context using CALCULATE function to only those records in&nbsp;'Foreign exchange rates' where [FX_CCY]="USD".&nbsp;Table = 
VAR dat = DATE(2016;1;1)
VAR pct = SUMMARIZE(
	ADDCOLUMNS(
		CALENDAR(
			dat
			;dat+99
		)
		;"pct"
		;[Date]-dat+1
	)
	;[pct]
)

VAR pct_value_calculate = ADDCOLUMNS(
	pct
	;"pct_value"
	;CALCULATE(
		PERCENTILE.INC(
			'Foreign exchange rates'[INDIRECT_FX_CCY_QUOTE]
			;[pct]/100
		)
		;'Foreign exchange rates'[FX_CCY]="USD"
	)
)

RETURN pct_value_calculate&nbsp;Unfortunately this does not work. It appears that when putting the PERCENTILE&nbsp;function inside a CALCULATE function, it no longer recognises the [pct] field on my calculated table.&nbsp;&nbsp;Has somebody a clue on how I can solve this problem? Or, if there is a totally different approach that is better suited to my scenario, I am all ears ;D&nbsp;Looking forward to your feedback,&nbsp;chefe&nbsp;

owenauger · Accepted Answer

Hi Chefe,&nbsp;I think your best bet is to&nbsp;create a variable to store the value of the current row's [pct] before calling CALCULATE:&nbsp;Table =
VAR dat =
    DATE ( 2016; 1; 1 )
VAR pct =
    SUMMARIZE (
        ADDCOLUMNS ( CALENDAR ( dat; dat + 99 ); "pct"; [Date] - dat + 1 );
        [pct]
    )
VAR pct_value_calculate =
    ADDCOLUMNS (
        pct;
        "pct_value";
        VAR CurrentPct = [pct]
        RETURN
            CALCULATE (
                PERCENTILE.INC (
                    'Foreign exchange rates'[INDIRECT_FX_CCY_QUOTE];
                    CurrentPct / 100
                );
                'Foreign exchange rates'[FX_CCY] = "USD"
            )
    )
RETURN
    pct_value_calculateI think a rough explanation is that when&nbsp;CALCULATE triggers a context transition (i.e. row context becomes filter context), any columns within the row context that were created with ADDCOLUMNS (like [pct]) are&nbsp;no longer accessible within the first argument of CALCULATE. This is because 'created columns' have no lineage so cannot be converted to filter context.&nbsp;&nbsp;This means you can refer to [pct]&nbsp;outside CALCULATE or in one of CALCULATE's filter arguments, but not in CALCULATE's first argument. So the only way I can think of to access the value of&nbsp;[pct] is to save it in a variable before calling CALCULATE.&nbsp;Owen :)

Forum Discussion

Related content

Calculated tables are not being refreshed automatically anymore

Calculate percentile from measure

Calculate percentile between tables

Calculating percentiles of a measure

Calculating Percentiles in PowerBI