Forum Discussion

nickchobotar's avatar
nickchobotar
Skilled Sharer
7 years ago
Solved

Dynamically return percentiles values

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 ph...
Need Help
  • TomMartens's avatar
    TomMartens
    7 years ago

    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

     

TomMartens's avatar
TomMartens
Super User
7 years ago

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's avatar
nickchobotar
Skilled Sharer
7 years ago

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] ) 
	            )
	        )
        )
   )