Dynamically return percentiles values

[nickchobotar]

Hello,

I would appreciate some help with building a virtual table that would dynamically calculate percentiles based on the Cartesian product of two physical (unrelated) tables. In my model my physical table are Sales and Percentiles. The percentile K values are coming from a Percentiles table which is one column table and contains K values.  The result of this table will be further used as a variable to incorporate additional business logic.   

Here is the end result of the virtual table. As you can see the Percentile Amount is calculated for each ClientID based on the Percentile K field which dynamically changes 3 times: 0.2, 0.5 and 0.8. 

Here is a link to the PBIX file: https://1drv.ms/u/s!AsgNvkRwqGC7hHq3YUsaKMQbByRo
Excel file: https://1drv.ms/x/s!AsgNvkRwqGC7hHs1qC0szXT9V_64

To sum it up. I am looking to build an in-memory table below.  ClientId and Amount is a CROSSJOIN of Sales and Percentiles tables and Percentile Amount is what I need a little help with.

Here in DAX Studio, you can see I am using an iterator PERCENTILEX  as I discovered it is not possible to dynamically feed K values into regular PERCENTILE function.  I believe this is the correct path to solve this.

 
DEFINE
    VAR crossjoinTbl =
        CROSSJOIN ( 
        	Sales, 
        	VALUES ( Percentiles[Percentile K] )
    	)
EVALUATE
ADDCOLUMNS (
    crossjoinTbl,
    "Percentile Amount", 
  	 PERCENTILEX.INC ( 
    		crossjoinTbl, 
    		Sales[Amount], 
    		Percentiles[Percentile K]
 	)    		
)
   	

Any ideas will be highly appreciated.

Thanks

[TomMartens · Accepted solution]

Hey,

this DAX statement ...

Table = 
var tblCrossJoin = CROSSJOIN('Sales', 'Percentiles')
return
ADDCOLUMNS(
    tblCrossJoin
    ,"percentileAmount"
        ,var currentClientID = 'Sales'[ClientId]
        var currentPercentile = 'Percentiles'[Percentile K]
        var sumAmount = CALCULATE(SUM('Sales'[Amount]))
        return
        PERCENTILEX.INC(
        FILTER(
            tblCrossJoin
            ,'Sales'[ClientId] = currentClientID && 'Percentiles'[Percentile K] = currentPercentile       
        )
        ,'Sales'[Amount] * 1.0, currentPercentile
    )
)
		

... helps to create this table ...

It looks pretty much the same as your expected result.

Hopefully it is what you are looking for.

Regards,

Tom

[nickchobotar]

Tom,

Thank you. Yes, that's a very good approach and plus I am getting back a very good query plan too. I was also exploring a noniterative approach with PERCENTILE and it works too, so there is a path in that direction too only needs tweaking the nested conditional statements.

DEFINE
    VAR tbl =
        CROSSJOIN (
            Sales, 
            VALUES ( Percentiles[K] )
        )

EVALUATE

ADDCOLUMNS (
    tbl,
    "Percentile Amount",CALCULATE(    
	        CALCULATE (
	            PERCENTILE.INC ( 
	                Sales[Amount], 
	                    if( VALUES(Percentiles[K]) = 0.2 , 0.2, 
	                    if( VALUES(Percentiles[K]) = 0.5 , 0.5, 
	                    if( VALUES(Percentiles[K]) = 0.8 , 0.8 
                )))),  
	            ALL ( Sales[Amount] ),
	            CROSSJOIN ( 
	                VALUES ( Sales[ClientId] ), 
	                VALUES ( Percentiles[K] ) 
	            )
	        )
        )
   )