olap,powerpivot,dax,powerbi,ssas-tabular , Calculating PERCENTILE in DAX - SOLVED

## Question:

Tag: olap,powerpivot,dax,powerbi,ssas-tabular

OK, so I have googled up a lot on this subject and keep ending up with formulas which are too damn slow. I am suspecting if I split the formula in steps(Creating calculated columns), I might see some performance gain.

I have a table having some numeric columns along with some which would end up as slicers. The intention is to have 10th, 25th, 50th, 75th and 90th percentile over some numeric columns for the selected slicer.

This is what I have till now for the 10th Percentile over the column "Total Pd".

``````TotalPaid10thPercentile:=MINX(
FILTER(
VALUES(ClaimOutcomes[Total Pd]),
CALCULATE(
COUNTROWS(ClaimOutcomes),
ClaimOutcomes[Total Pd] <= EARLIER(ClaimOutcomes[Total Pd])
)> COUNTROWS(ClaimOutcomes)*0.1
),
ClaimOutcomes[Total Pd]
)
``````

It takes several minutes and still no data shows up. I have around 300K records in this table.

Do let me know if this information is insufficient and some more data is required.

I figured out a way to break the calculation down in a series of steps, which fetched a pretty fast solution.

For calculating the 10th percentile on `Amount Paid` in the table `Data`, I followed the below out-of-the-book formula :

Calculate the Ordinal rank for the 10th percentile element

``````10ptOrdinalRank:=0.10*(COUNTX('Data', [Amount Paid]) - 1) + 1
``````

It might come out a decimal(fraction) number like 112.45

Compute the decimal part

``````10ptDecPart:=[10ptOrdinalRank] - TRUNC([10ptOrdinalRank])
``````

Compute the ordinal rank of the element just below(floor)

``````10ptFloorElementRank:=FLOOR([10ptOrdinalRank],1)
``````

Compute the ordinal rank of the element just above(ceiling)

``````10ptCeilingElementRank:=CEILING([10ptOrdinalRank], 1)
``````

Compute element corresponding to floor

``````10ptFloorElement:=MAXX(TOPN([10ptFloorElementRank], 'Data',[Amount Paid],1), [Amount Paid])
``````

Compute element corresponding to ceiling

``````10ptCeilingElement:=MAXX(TOPN([10ptCeilingElementRank], 'Data',[Amount Paid],1), [Amount Paid])
``````

Compute the percentile value

``````10thPercValue:=[10ptFloorElement] + [10ptDecPart]*([10ptCeilingElement]-[10ptFloorElement])
``````

I have found the performance remarkably faster than some other solutions I found on the net. Hope it helps someone in future.

# Related:

## Do I need a cube?

sql-server,ssas,reporting,olap
We have a content ingestion system which receives (mobile) digital contents of different types (Music, Ringtone, Video, Game, Wallpaper etc) from various providers (Sony, Universal Music, EA Games etc) and then dispatches them across several online stores (e.g. Store1, Store2 etc). The managers want to know how many of each...

## Sort entire Pivot table in Excel 2013 by column

sorting,excel-2010,pivot-table,excel-2013,powerpivot
I have a Pivot Table with many columns. I want the Pivot the ability to sort one of the columns in a way that the whole column is sorted and not the relative position in the hierarchy. Example: NAME PRODUCT SUM Joe A 400 Joe B 200 Joe B 300...

## SSAS report action to pass multi-value list values to a SSRS report parameter

excel,reporting-services,ssas,mdx,olap
I have searched many hours attempting to find an end-to-end solution to this problem. I want to create a report action in an SSAS OLAP cube that generates a list of formatted values to pass into an SSRS report parameter that accepts a multi-valued parameter list. This would be applied...

## Microsoft Power BI Designer data model to Excel or PowerPivot

excel,microsoft,powerpivot,powerbi
Is there a way to get a Microsoft Power BI Designer data model into Excel to work with in Powerpivot? Thanks in advance....

## SSD performance benefit in OLTP vs OLAP

olap,ssd,oltp
In which system is SSD disk better over HDD as far as performance is concerned ? An OLTP or an OLAP system ? My guess is that in OLTP an SSD disk is more valuable, because transactions are constantly occurring and we need the non-sequential read-write speeds. Is this correct...

## how to create Pivot query for this?

sql,sql-server,sql-server-2008,pivot-table,powerpivot
I need output like this. In this no limit of shape; for example : [RD],[PC],[EM],[SE],[PR],[CM] My data looks like this : POINTER_RANGE SHAPE_NAME PCS CTS VALUE 0.30-0.39 RD 6 1.826 3503.17 0.40-0.49 RD 3 1.238 2837.62 0.50-0.69 PR 1 0.504 776.16 0.50-0.69 RD 5 2.618 8639.58 0.70-0.89 PC 5 4.109...

## XML for Analysis (XML/A) format of member names?

olap,mondrian,xmla,iccube
I have two different XML/A providers, Mondrian and icCube. The tuples for a time dimension contain the unique name for the member, but the format of the member name is different: Mondrian: <UName>[Time].[2004].[QTR2].[Apr]</UName> <Caption>Apr</Caption> [Time] is the name of the hierarchy [2004] is the name of the ancestor at the...

## PowerPivot % Difference from previous month

excel,date,pivot-table,powerpivot
When trying to get the percent difference from one month to the next, January (or the first month in the dataset) will always show as blank. How would I go about making Excel look for the previous month of last year? I am using DateStream as my date table. Issues...

## Pivot4j/Olap4j adding condition to where clause of generated query

olap,olap4j
I have a ROLAP cube - Pivot4j 0.9, Olap4j 1.1.0, MySQL. When I run the following MDX I can see an additional condition in the where clause which causes the resulting statement to return no rows. SELECT Hierarchize({[Measures].[Unique Users]}) ON COLUMNS, NON EMPTY Hierarchize([dimBrowserType].AllMembers) ON ROWS FROM [Sessions] Resulting SQL......

## PowerBI hierarchy creation without the cloud

excel,hierarchy,powerpivot,powerview,powerbi
Right now we can create hierarchies in PowerPivot but I'm pretty sure I can't do it in PowerBI. I could create a Data Model in PowerPivot and use that for PowerBI I think. Is there any other work around that could be more desktop based? Thanks! Mike...

## Approaching mixed granularity date dimensions for operational periods in cube design

ssas,olap,olap-cube
I am building a cube in SSAS, modelling (amongst other things) activity of engineering teams. I have a fact table (TeamActivity), with facts such as Mileage and TimeOnSite on a DAILY granularity. This references a date dimension table (DimDate). DimDate contains typical attributes so data can be analysed by calendar/fiscal...

## select data from olap MDX query

ssas,mdx,olap,olap-cube
Please help to understand how MDX query works. I have connected to cube using excel and construct a mdx query. In short finally I need to get the table like this: 12.01.2015 +-------+-------+-------+-----+-------+------ | 00:00 | 01:00 | 02:00 | ... | 23:00 | TOTAL --------+-------+-------+-------+-----+-------+------ Ivan | null |...

## How to link table row content to source in Power View

excel,powerpivot,powerview,powerbi,powerquery
I am currently able to use Power View to view, filter, and highlight my data. However I haven't figured out a way to link my table rows to the data source (i.e. tables in other tabs of the Excel spreadsheet). so that if I double-click on a row, Excel will...

## Difference between sql query aggregation and aggegration and querying an OLAP cube

analytics,data-warehouse,olap,olap-cube,star-schema
I have a query with respect to the advantages of building a OLAP cube vs aggregating data in database table for querying ,data of say 6 months and then archiving the sql table later for analytics purpose. Which one is better, table or OLAP cube? and why since I can...

## Is it possible to disable multiple selection of dimension values in OLAP cubes in SSAS?

sql-server,ssas,olap
I'm designing a MOLAP cube in SSAS and one of my dimensions has a very high cardinality >1000 members. I want to disable multiple selection on this dimension and only allow users to select one member at a time. Currently, when users browse a cube through SSMS, they can drag...

## PowerPivot - How to filter columns yet retain grand total

powerpivot
in the table below, I would like to graph a subset of the columns, specifically the ones highlighted in green. however, I would like to compute the percentage based upon the grand total of the whole set. filtering columns rescopes the data set and hence the denominator. I would like...

## Intersect Select Query in MDX

ssas,mdx,olap
I want to have customers intersection in two mdx querys. 1. SELECT [Measures].[Cs] ON 0 ,NonEmpty([Customers].[Customer].MEMBERS) ON 1 FROM [sfe cube] WHERE ( [Calend].[Period].&[201506] ,[Customers].[BP Territory].&[38UZ1] ,[Materials].[Brand].&[Coca-Cola] ); 2. SELECT [Measures].[Cs] ON 0 ,NonEmpty([Customers].[Customer].MEMBERS) ON 1 FROM [sfe cube] WHERE ( [Calend].[Period].&[201506] ,[Customers].[BP Territory].&[38UZ1] ,[Materials].[Brand].&[Fanta Orange CSD] ); My attempted...

## PowerPivot : Use Relative address for an Excel Connection

excel,powerpivot
I am importing data from external excel file into PowerPivot (Using option "From External Sources -> Excel File". When I update data from in PowerPivot, it pulls data correctly from external excel file. Problem occurs when these two excel files (one having PowerPivot and another with source data) are deployed...

## DAX for MAX of a group

visual-studio-2012,max,powerpivot,calculated-columns,dax
I have two data columns: Record | Record Version ------------------------ 1 | 1 1 | 2 1 | 3 2 | 1 2 | 2 2 | 3 2 | 4 3 | 1 3 | 2 3 | 3 4 | 1 4 | 2 4 | 3 4...

## How to bulk-import all Power Query workbook connections into PowerPivot?

excel,excel-2010,powerpivot,powerquery,powerbi
Within PowerPivot for Excel, under the 'Design' tab, there's an option to use 'Existing Connections' as a data source for Power Pivot. Here we can select our carefully shaped & managed Power Query connections (e.g. to SQL Server, Web pages, flat files). However this interface only permits the selection of...

## MDX - TopCount plus 'Other' or 'The Rest' by group (over a set of members)

ssas,mdx,olap
I've got requirement to display top 5 customer sales by customer group, but with other customers sales within the group aggregated as 'Others'. Something similar to this question, but counted separately for each of customer groups. According to MSDN to perform TopCount, over a set of members you have to...

## Which NoSQL technology can replace MOLAP cubes for instantaneous queries?

mongodb,cassandra,nosql,olap,hbasestorage
I was wondering if you could tell me which NoSQL db or technology/tools should I use for my scenario. We are looking at replacing our OLAP cubes based on SQL server Analysis services with an open source technology coz the data is getting too huge to manage and queries are...

My understanding is that AdomdConnection initializes a connection with a remote database. This class opens it based upon the ConnectionString. The AdomdCommand then seems capable of setting a CommandText and Executing said command, similar to executing a query. Is this understanding correct? But how are the two related? Do I...

## How to make a calculated member in MDX for SSAS?

I have a problem in my calculation and can't solve. Example: Our company sells about 100 products, divided into categories and product ID. Product ID is the smallest granularity. I need to show how this product list we sell in each city. See the picture: Overall, without showing the category...

## Filtering Data In PowerPivot on Sharepoint

excel,sharepoint,powerpivot
When viewing a PowerPivot workbook natively all is well. However when I save it to Sharepoint and view it via a browser, I find that the option to filter data by typing in a value is no longer present. (All there is is an extremely LONG list of values which...

## Calculate the number of business days between two dates in power pivot

powerpivot,dax,powerbi,powerquery
I am looking for a formula to calculate the number of weekdays/business days between two dates in power pivot. I do the same in T-SQL using the following query DATEDIFF(dd, Date1, GETDATE()) - (DATEDIFF(wk, Date1, GETDATE()) * 2) - CASE WHEN DATEPART(dw, Date1) = 1 THEN 1 ELSE 0 END...

## DB2 restrict analytic function to subselect

sql,db2,olap
I often encountered the situation where I wanted to restrict the action of an analytic function (or OLAP in DB2) to a certain subset of the present data. Here is an example: WITH MY_TABLE AS ( SELECT 1 AS FIELD1, 'A' AS FIELD2, 1 AS FIELD3, 'X' AS FIELD4 FROM...

## Rank Values of one column by filtering on 2nd column in DAX

powerpivot,dax
I'm trying to use the RANKX formula rank the values of one column, but filtered for the value of a second column. In this example, col2 is a simple counter running in ascending value. I'm trying to find item_id's Rank value relative to the col1. col1 col2 1001 8001 1001...

## Easiest way to programmatically generate MDX rowcount query?

sql,mdx,olap
Right now I'm dealing with a program that can generate and return SQL or MDX queries (depending on the source database of the queries). I'm working on adding a feature that counts all the rows returned by a given query. Now, I have some small background with SQL, so I...

## Top 5 and Bottom 5 records in Power Pivot

powerpivot,dax,powerview,calculated-measure
I am trying to show top 5 records and bottom 5 records in two separate tables in Power View. I have created a calculated measure to show the records. But it doesnt giving me the required result. My Data Set is:- ID Name Rating OverallRating Ranking 1 A 124 2...

## How to add index to CitusDB's cstore_fdw?

postgresql,indexing,olap,citusdb
I'm currently building an OLAP database in postgres and want to compare the performance of a column-store vs row-store database. CitusDB open-sourced its columnar-store extension cstore_fdw so I'm comparing database performance with and without this extension. The example shows how to make a test db and query it. I have...

## How to construct a pivot table that contains values based on certain conditions

excel,vba,pivot-table,powerpivot
I want to construct a pivot table from a huge data-set. Now I want to construct the pivot table that consists of values based on certain specified conditions, for example, the entries with age>25 will only be populated in the pivot table. How to do that without manually filtering out...

## SSRS mdx report: use dimension on columns

reporting-services,mdx,olap,dimension,rdl
This simple mdx query do work in SSMS: SELECT CrossJoin({[Measures].[Qnt]}, {[Sales_step].CHILDREN}) ON COLUMNS, [City] ON ROWS FROM [SALES_PIPE] But it fails to run within Visual Studio for rdl report. An error accures: The query cannot be prepared: The query must have at least one axis. The first axis of the...

## Apache Kylin - wrong output at the first step of cube building

I'am trying to build my first cube using Apache Kylin, everything goes fine until last step where I'm getting error: java.lang.IllegalStateException: Can't get cube source record count. at com.google.common.base.Preconditions.checkState(Preconditions.java:149) at org.apache.kylin.job.cube.UpdateCubeInfoAfterBuildStep.doWork(UpdateCubeInfoAfterBuildStep.java:104) at org.apache.kylin.job.execution.AbstractExecutable.execute(AbstractExecutable.java:107) at...

## Count of Rows Based on Their Most Recent Value in Excel PowerPivot Using DAX

excel,powerpivot,dax,bism
Is it possible to get a distinct count of rows based on a field's most recent value in a PowerPivot table using DAX? I have a transactional table that tracks professionals with hire and transfer dates. I would like to get a count of the professionals based on their most...

## many to many powerpivot relationship

many-to-many,relationship,powerpivot,powerview
I have 2 data sets which i would like to create relationships for (there is a third i would like to join but the first 2 are priority). They all have a common link that is design_ID. I did have a picture showing what i have tried. but i do...

## Calculating PERCENTILE in DAX - SOLVED

olap,powerpivot,dax,powerbi,ssas-tabular
OK, so I have googled up a lot on this subject and keep ending up with formulas which are too damn slow. I am suspecting if I split the formula in steps(Creating calculated columns), I might see some performance gain. I have a table having some numeric columns along with...

## How to create Power View report tiled by multiple KPIs?

excel,powerpivot,dax,powerview,kpi
I am trying to create a Power View report tiled by KPIs, is this possible? An example of my raw data: Company ID Employee ID Measure numerator denominator 1 01 1 2 5 2 04 1 3 6 3 02 1 0 5 4 03 1 1 2 1 01...

## SSAS cube: force Budget measure granularity drill down to the leaf level of calendar dimention (split Quarter Budget between Monthes, Weeks and Dates)

ssas,hierarchy,olap,granularity
Our Budget/Plan has Quarter grane, while Fact sales data is stored Dayly. To build Plan-Fact reports for Monthes, Weeks and Dates - I need to split Budget evenly between Calendar dates. How can I do this within a Cube? After hours of googling I've found references to [IgnoreUnrelatedDimensions] property of...

## Pivot Table with multiple rows all having the same level hierarchy

excel,reporting,pivot-table,powerpivot
I have imported a bunch of data using PowerQuery into a single table and am building dashboard reporting. I have been using Pivot Tables to build my reports, which has worked fine so far. However, I've come to a point though where I want to simply show the count of...

## How to create nested tiles in Power View

excel,powerpivot,powerview,powerbi,powerquery
I am currently able to use the tile feature in Power View to view data much more quickly. However I haven't figured out a way to have nested tiles to further drill down into the relevant data. For example, I want a tile strip at the top of my view...