Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] ) ) ) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |