Percentile Calculation in Direct Query Mode

[lauramma]

I'm wondering if there's a way to create percentile measures in direct query mode. I saw quite a few postings sharing how to use Percentile.Incl() / Percentile.Excl() functions to create percentile columns for the existing columns in the table. But the same function is not available in Direct Query mode. Since uploading the reports created in import mode would upload the data in the cloud as well, it is not ideal for the level of security we need to keep for our data.

I already explored the percentile line in some of the visualization, but it only seems to calculate the percentile for the whole range of x-axis values included in the graph, so it's not granular enough for my analysis. I need the percentiles to be calculated for each data point on my x-axis. For example, if I plot a graph by month for a measure, I need the 90 percentile to be calculated for this measure for every single month.

[v-huizhn-msft]

Hi @lauramma,

What data source do you use? Can you create a measure? You you can, please try the following formula to create a measure and check if it works fine.

percentage=DIVIDE(sum(Table[avlue]),sumx(ALL(Table),Table[avlue]))

Best Regards,
Angelia

[lauramma]

Hi Angelia, I can create measures in the tables I brought in from SQL server, as long as the measures are using DAX functions that are available in Direct Query mode. The sum() and DIVIDE() functions you listed there both seem to be available. But that doesn't give me the percentiles for a particular value in the table. Ideally, I need to calculate the percentiles dynamically in the BI layer, rather than build it into my SQL tables, as I'd need this measure at different hierarchical levels.

[v-huizhn-msft]

Hi @lauramma,

Could you please share your sample table and list the expected result, so that we can post the detailed solution which is close to your requirement?

Best Regards,
Angelia