Power Pivot :: Running Rank Calculated Column That Obeys Context Filters
Jul 13, 2015
I am looking to add a column to one of my tables that displays a running rank of how many times a customer has ordered in a given period.
I currently have such a column however this column ranks against ALL of the orders that a customer has placed and ignores filters, whereas I need one that ranks based on the filters that are active at any given time.
The current formula is:
CustOrderCountPersistant=RANKX(FILTER('Q1 Data Set',[k1_customer_id]=EARLIER([k1_customer_id])),[order_id],[order_id],1,DENSE)
For example, if I am looking at a full years worth of data and a customer has placed 10 orders in that period this formula will add a 1 in the column for first order, a 2 for the second and so forth all the way to 10, the last order.
However it will give me exactly the same results if I filter the data to just one month of that year where they may have order only 2 orders.
In this scenario I want to have another column with a table that is filter sensitive and would show 1 for the first order and 2 for the second order.
Now, I do understand that the issue here is probably the FILTER() I have on as, if I understand correctly, that means all other filters are ignored. My attempts at reworking the formula to remove this have been unsuccessful (such as using a CALCUALTE and trying to use filter properties within that forumula).
To explain the context - I want to create a measure that counts how many customers have placed x amount orders in y number of days e.g. how many customers have placed 2 orders in 30 days.
I try to calculate the turnover of an employee based on all sales on this customer and the hours spends working for this customer.To simplify, I have 2 tables.General Ledger table.I have customer no and sales.And activity table,I have customer no, hours and users.To be able to calculate the average of a customer, I made an average value based on the sales/divided by the hours on activity table.Customer 1 sales of 100$.Value of the customer is 10$/H -> John 40$ / Mike 60$.How can I use the information 10$/h to use it with the hours from a employee.
I have written a simple DAX calculated column formula using SEARCH and got an error: It tells the text "PWP" cannot be found but as you can see the screen shows this text exsist within "Promotional Claim"The same happens when I try FIND function.
Country State Rank India Kerala 1 India Kerala 2 India Kerala 3 India Tamil Nadu 1 India Tamil Nadu 2 India Orissa 1 India Orissa 2 US Florida 1 US Florida 2 US NewYork 1
I have to generate rank like this in power pivot. How can I achieve it?
In a calculated column I am trying to get a scalar text value from a lookup to another table. This works quite well when getting numerical values with the following formula:
But as soon as I substitute the numerical column by a string column, #error results.
I also want to mention that the above query yields only one row as a result. It should be simple to return the value of one of the columns but after searching for quite some time, I could not find any function for that.
I have a calculated measure that is "Cost Per Patient:=sum([ActualCost]) / sum(Patients[PatientCount])" and it works fine. I'd like to be able to create another calculated measure based on "cost per patient" that uses min, max and average of "cost per patient" but dax wont let me.
I've read about dax calculate but not sure how to use it in my situation. Here is background on the problem:
There are 2 fact tables, prescription cost and patient count. Both are related by a practice dimension. The practice dimension has a geography hierarchy of county - town - practice.
The "Cost Per Patient" works fine with the geography hierarchy. By that I mean it shows the correct value when looking at the different levels. For my new calculation, for example looking at the list of counties, I want to use the min, max and average of "Cost Per Patient" of all counties. To break the problem down, I was going to start with creating and testing separate calculated measures for min, max and average and that's where I am stuck.
My understanding of the problem is it's something to do with the context of where the calculation is being made. As I want to perform the min, max and average on all the children of the parent. This article explains it but I can't figure out how to apply it to my situation [URL] ....
We have a client that wants to take the Max value of a count at the Product level, but then for each Brand (Parent of product), sum the Max values for that particular slice. The example below shows the Max sales per month over the last 6 months.
Prod1 had it's best month in Month2 (20 units) and Prod2 had its best month in Month4 (30 units). Brandx should roll up to 50 units. Can DAX handle this in a single Calc Measure?
I have a calculated measure in Power Pivot that takes too long to run that it would max out my RAM when running it in Excel 2016 (for some reason it actually manages to run in Excel 2013 but still takes a long time). How to optimise the measure? I have shared the measure below. it is the Unique Leads (MTD) measure that I need to run in a pivot table that is broken down by everyday in a month. The Unique Leads (MTD) measure basically calculates the month-to-date unique leads where a unique lead is defined as an email address that has not appeared in the 'application' table thus far in a particular month.
This is so trivial but I cannot figure out where the calculated fields/measures area is in the PP window. Is there something to toggle it on and off? I used PP in Excel 2013 before and the area I'm talking about was at the bottom of the PP window. Now I'm with a new company who has Excel 2010 so I downloaded the PP add-in for Excel 2010. It is version 10.50.4000.0. This add-in does not have a calculated fields/measures are at the bottom of the PP window. Is it just a matter of 2010 vs. 2013 or is there a way to turn the frame area I'm talking about on?
I have a requirement in Power Pivot where I need to show value based on the Dimension Column value.
If value is Selling Price then Amount Value of Selling Price from Table1 should display, if Cost Price then Cost Price Amount Should display, if it is Profit the ((SellingPrice-CostPrice)/SellingPrice) should displayMy Table Structure is
A Store A L 100 6 600 A Store A S 150 4 600 A Store A M 160 7 1120 B Store A L 640 15 9600
[Code] ...
Ignoring that gives another figure for productgroup B. In the pivot, I should see both measures at whatever attribute, except for the measure that excludes color will be null if tried split on color
ProductGroup Amount Quantity (ProductGroup, Color, Store, Size) (ProductGroup, Store, Size) A 410 17 2320 2320 B 640 15 2820 9600 C 170 5 430 430
I have created 60 queries and added them to my data model in Power Pivot/Excel 2016. I created some calculated fields for one of my queries but I would LOVE to know how to just copy these across all queries in the data model instead of having to create them in the data model for one query at a time. That will take forever.
What I'm looking to do is very much the image below. I need to bucket the DOS. I cannot do a calculation in a column because the DOS needs to recalculate based on filter changes or how graphs are setup. My DOS is a calculated field. When I try to create a calculated field to create the buckets I can't use it in a pivot table or powerview.
For some of my calculations it is mandatory to get the first and the last date, filtered by the slicer type "timeline".
This works well as long as the date is not used on rows or columns ...
As soon as I'm using theDate within the rows this very simplistic dax measure doesn't work ...
I'm wondering how to determine the StartDate (from the timeline) and the EndDate (from the timeline) when "thedate" is actively contributing to the filtercontext.
In my opinion it is not another example of the "event in progress" situation, due to the fact that start and end dates are actively selected by the user and can not be derived from within the date model.
I can't seem to upload images so here's the link: [URL] ....
I need to get the last value of # Orders week avg in the current context (the highlighted value). The calendar dimension here is a week level one so I don't think I can use LASTNONBLANK. The measure is from the facts_sales table which is linked to a calendar dimension via a date key.
I tried below measure to get the YTD running product and its working fine for me. but I found one issue that its give me slow performance while I see running product by different dimension level.
Creating a burn down chart using a running total of cumulative hours with the following formula:
CumulativeHoursLeft:=CALCULATE ( SUM('Projects'[Budget hours]) - SUM ( 'hours'[Hours] ), FILTER ( ALL ( 'hours'[Date] ), 'hours'[Date] <= MAX ('hours'[Date]) ) )
Works great except that in a Line Chart using [Date] as the Axis and CumulativeHoursLeft as the value, I get these spikes on days for which the employee reported no hours. I do know what exactly the measure is doing in this instance and I do not get this in a table, those dates simply do not appear. I have tried both Categories and Continuous for the Line Chart. I have also tried filtering where [Date] is not blank.how to get rid of the spikes?
I am trying to show images in a product listing in power view.I work with an excel 2013 desktop version based on an office 365 pro account.I did the following steps:
import of an excel file with an article list via power query and loading the data to the data model import jpg images from a folder via power query, setting content as binary type and loading the data to the data modellinking both tables in power pivot--> manage via the image namesetting the table behavior for the images table under power pivot --> manage --> Advanced (e.g. Default Image: Content)opening power view and building article cards with article number and imageProblem: only a camera icon shows up in power view
Is there a solution with a desktop version?Can I use my Office 365 Pro account to make it work? How?Why is there no solution showing images in a pivot table?Link to Dropbox with power pivot files
Q1-2013 Q2-2013 Q3-2013 Q4-2013 2013 Total Distinct Count Member Distinct Count Member Distinct Count Member Distinct Count Member Member Months AMBULANCE - LAND 264 301 355 352 1272 AMBULATORY SURGICAL CENTER 16 38 30 34 118 COMMUNITY MENTAL HEALTH CENTER 6 7 11 13
I have the above data, and want to create a column adding up the Distinct count member of each quarter. The number of distinct count member of each quarter is from a measure. The result I want to create is the column of 2013 Total Member Months as shown above. How to create a dax formula.
Using PowerPivot I created a connection to a view on a SQL Server database. All fields imported correctly.A column has since been added to the view. How do I get this to appear in my PowerPivot?
I have two tables shown below and I wish to add a calculated column to Table 1.
Table1 Table2 ID Activity Activity 1 Activity 2 Activity 3 etc etc etc Total 1 Activity 1 values values values Total value 2 Activity 2 values values values Total value 3 Activity 3 values values values Total value etc
I want each row in the new column to show the corresponding activity divided by the sum of the total. So in the row with Activity 3 I want the formula SUM(Table2[Activity 3])/SUM(Table2[Total]). Also the number of activities will vary.
I am actually wanting to calculate a more complicated formula which calculates the correlation .
This is an SPC chart controlled by a slicer that operates a powerpivot table. This is then copied (by cell formula) into a "normal" table where the average, UCL, LCL and Erlang are calculated which are just basic calculations involving average and standard deviation. To make it work the values in the Average, UCL,LCL and Erlang must be repeated all the way down the table to create the chart. In the current format it works well and using a standard table keeps the chart range dynamic.
However this is a very clunky solution involving repeating the data tables in excel. I need to create dozens of charts and it will get large and slow.I would like to create the whole thing in a powerpivot table using measures so i can use powerpivot charts and ultimately powerBI. The data column PasID is a text column so I need a measure that calculates the "count of PasID" for each day(the row labels) and then repeats that value down a whole column in the same way the standard table does. I couldn't figure out how to get the correct number repeat down the whole column, which measures to use,Whether to create calculated columns in the data model or any of it. SO I need to be able to get a count of a text column then display the average of that count in a second column all the way down.
I've been trying to use a simple calculate forumla when adding a column CALCULATE(SUM([Credit]), FILTER(ALL([Date],[Date]<=MAX([Date]))) or just a CALCULATE(SUM([Credit])
And I have always the same error
Calculate is not recognized. I've try it on PowerBI and Excel ...
I am try to count number of items that will result by filtering a date column (one Date column). Ex Column "Created Date" between 1-Sep-2015 To 30-Nov-2015.
I am unable to get any function that is getting right value. The below function return 40, however the actual value when i do manual filter and count is 132.
I have 2 excel tables files.One table has info about sales by country, by model, by date...and the other table has units by date.Obviously, the common key is "date"....and by creating a relationship, I can add "units" to my combined resulting table.However, I can't create the relationship. Excel keeps telling me: "the relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values".
I'm trying extract a column from the table based on certain Conditions: This is for PowerPivot.
Here is the scenario:
I have a table "tb1" with (project_id, month_end_date, monthly_proj_cost ) and table "tb2" with (project_id, key_member_type, key_member, start_dt_active, end_dt_active).
I would like to extract Key_member where key_member_type="PM" and active as of tb1(month_end_date).
I have a table ComponentPeriod. In it we have the combination of a component (e.g. A,B,C ) and a period (2014 Q1, 2014 Q2, 2014 January etc)I want the periods to be in descending order (2015 Q4, 2015 Dec, 2015 Nov, 2015 Oct, 2015 Q3 ... etc) and so I need to create a sequential number series to allow this to happen (as we can only order in the client tools by a single column - and so I guess the technique I'm looking for is used a lot to produce these types of "order by" columns)
Which was fine when I was referring to a table where Periods where distinct directly but now I have denormalised this for ComponentPeriod so I need something a little more sophisticated Whats the best way to get a sequence with perhaps some partitions in across a subset of distinct columns (I guess from SUMMARIZE or similar)
even though there may be multiple records in ComponentPeriod that have the period 2015 Q4, but I want them all to have the value Sequence value of 1? I've got as far as:
Is it possible to include a currency symbol in an amount-field in PowerPivot/Pivottable based on a Currency column in a table? Something as the same as with SSAS MD. And I don't want fixed values in my code.
I have several reports in a Power View Gallery. In Gallery view, most of the reports show the "Open New Excel Workbook", the "Create Power View Report", and the "Manage Data Refresh" buttons on the right side of the report list. Why would some reports not have these buttons available? In the attached image you can see one report with the buttons and one without the buttons.
