DAX Equivalent for NTILE

DAX DAX Calculations
1

Hi All,

Can you assist me with doing the DAX version of the below partial SQL Query

case ntile(10) over( order by Score asc)
when 1 then ‘Top 10%’
when 2 then ‘Top 20%’
when 3 then ‘Top 30%’
when 4 then ‘Top 40%’
when 5 then ‘Top 50%’
when 6 then ‘Bottom 50%’
when 7 then ‘Bottom 40%’
when 8 then ‘Bottom 30%’
when 9 then ‘Bottom 20%’
when 10 then ‘Bottom 10%’
else ‘Check’
end as ‘Vehicle_Score_Grouping’,

If any info is needed please let me know
I have looked at https://docs.microsoft.com/en-us/dax/percentilex-exc-function-dax
I do not understanding this Dax Function

I am building a Dashboard using vehicle telemetry data and based off the counts of harsh braking , acceleration, Cornering, Impacts alerts etc I have come to a score (This Scoring calculation could be another topic I would like to dive into and discuss with the people of ENDA).

I would like to group the scoring of each vehicle as in the SQL query above for the Vehicle Summary page. Its the only thing im missing to complete this Dashboard.
I can do this in SQL as intended but need to keep this in Power BI as all my calculations are there.

2

Hi @KyleJ.

It would really help to see the dataset; could you please provide as many as you can of:

  • Your work-in-progress PBIX file, using sanitized data if necessary
  • Your dataset as an Excel file (again, sanitized if necessary)
  • A detailed mock-up (marked-up screenshot or Excel file) of your desired outcome.

Greg

3

Thanks for posting your question @KyleJ. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

4

Hi Greg,

100%
let me touch base with CIO, I will check if I can share if I sanitize the data and clean the PBIX file.
Hopefully, I can get the go ahead. I would love to dive into this with you guys.

5

Hi @KyleJ, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

6

HI Team,

Ok cant do the full show and go through the Dashboard I’m working on.
I would still like to answer my question above, lets do what we can.

Updated explanation and example below:

I am building a Dashboard using vehicle telemetry data and based off the counts of harsh braking , acceleration, Cornering, Impacts alerts etc I have come to a score .

I am using this score to Rank each of the vehicles
Having this rank I can show the driver where he stands compared to all

image

I would like to group the scoring into percentile groups. what I have called Vehicle Rank Grouping below

image

example of the result im looking fo

image

So i just want to add the percentile group description
Below is sample SQL Script to Generate the result set above,

drop table if exists #Ntile_Test

select *
into #Ntile_Test
from (
select 1560239 as ‘ID’, 27 as ‘Score’
union
select 1650873, 79
union
select 1571259, 150
union
select 1646625, 191
union
select 1654919, 227
union
select 1650835, 236
union
select 1639407, 241
union
select 1650782, 383
union
select 1656599, 395
union
select 1646271, 501
union
select 1650920, 559
union
select 1644208, 651
union
select 1655471, 657
union
select 1627985, 677
union
select 1630003, 694
union
select 1507588, 722
union
select 1649699, 864
union
select 1650999, 907
union
select 1653352, 908
) x

select ID, Score,
case ntile(10) over( order by Score asc)
when 1 then ‘Top 10%’
when 2 then ‘Top 20%’
when 3 then ‘Top 30%’
when 4 then ‘Top 40%’
when 5 then ‘Top 50%’
when 6 then ‘Bottom 50%’
when 7 then ‘Bottom 40%’
when 8 then ‘Bottom 30%’
when 9 then ‘Bottom 20%’
when 10 then ‘Bottom 10%’
else ‘Check’
end as ‘Vehicle_Rank_Grouping’,
Rank() over(order by Score asc) as ‘Vehicle_Rank’
from #Ntile_Test

Created a sample PBI Report so we can all use that to work with.
We need to do what I have done in SQL above in DAX
EDNA - Ranking Percentile Groups.pbix (39.0 KB)

I hope I have explained this in some sensible way.
if any more info is needed - please let me know.

7

Hi @KyleJ,

Welcome to the forum!
Thanks for providing a PBIX with expected outcome that’s very helpful. :+1:

Here’s a way to add the percentile group descriptions using DAX.

First added a Ranking column

image
image504×613 20.2 KB

And then the Ranking Group column

image
image765×765 41.3 KB

Here’s your sample file. EDNA - Ranking Percentile Groups.pbix (42.3 KB)
I hope this is helpful

5 Likes
8

Thanks that’s exactly what I need.
Your answer is so simple !!!

Thank You.