Forum Discussion

lauramma's avatar
lauramma
Frequent Visitor
8 years ago

Percentile Calculation in Direct Query Mode

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 c...
Need Help
v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
8 years ago

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's avatar
lauramma
Frequent Visitor
8 years ago

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's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    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