Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
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] ) ) ) ) )
User | Count |
---|---|
98 | |
90 | |
78 | |
71 | |
64 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |