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



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".

                                     VALUES(ClaimOutcomes[Total Pd]),
                                               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)


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.


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

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...

SSD performance benefit in OLTP vs OLAP

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...

Intersect Select Query in MDX

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...

Sort entire Pivot table in Excel 2013 by column

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...

Approaching mixed granularity date dimensions for operational periods in cube design

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...

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

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...

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

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...

Microsoft Power BI Designer data model to Excel or PowerPivot

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

How to link table row content to source in Power View

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...

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

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...

Pivot4j/Olap4j adding condition to where clause of generated query

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......

Filtering Data In PowerPivot on Sharepoint

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...

PowerBI hierarchy creation without the cloud

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...

PowerPivot - How to filter columns yet retain grand total

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...

Pivot Table with multiple rows all having the same level hierarchy

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...

SSRS mdx report: use dimension on columns

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...

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

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...

DB2 restrict analytic function to subselect

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...

Easiest way to programmatically generate MDX rowcount query?

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...

DAX for MAX of a group

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...


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...

PowerPivot % Difference from previous month

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...

select data from olap MDX query

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 construct a pivot table that contains values based on certain conditions

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...

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

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...

How to add index to CitusDB's cstore_fdw?

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...

Which NoSQL technology can replace MOLAP cubes for instantaneous queries?

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...

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

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 : Use Relative address for an Excel Connection

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...

Difference between AdomdConnection and AdomdCommand classes

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...

Rank Values of one column by filtering on 2nd column in 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...

How to create nested tiles in Power View

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...

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

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...

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

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...

many to many powerpivot relationship

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...

Do I need a cube?

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...

Top 5 and Bottom 5 records in Power Pivot

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 create Power View report tiled by multiple KPIs?

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...

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...

how to create Pivot query for this?

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...