Power Pivot :: DAX - Min / Max And Average On Calculated Measure
Jun 5, 2015
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] ....
View 5 Replies
ADVERTISEMENT
Sep 21, 2015
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?
Brandx 50
Prod1 20
Prod2 30
Brandy 60
Prod5 40
Prod6 20
View 2 Replies
View Related
Nov 6, 2015
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.
Unique Leads (MTD):=[CumulativeUniqueApps]-CALCULATE([CumulativeUniqueApps], filter(allexcept('application', Affiliate[Affiliate], 'Lead Type'[Loan Type]), CALCULATE([CumulativeUniqueApps], filter('application', EARLIER([Date])< [Date]))))
CumulativeUniqueApps:=calculate(DISTINCTCOUNT([Email]), filter(allexcept('application', Affiliate[Affiliate], 'Lead Type'[Loan Type]), CALCULATE(DISTINCTCOUNT([Email]), filter('application', earlier([Date])<=[Date]))))
View 16 Replies
View Related
Jul 9, 2015
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.
View 3 Replies
View Related
Sep 3, 2015
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
Table1:-
Table2:-
My Report Output should be look like
If tried the below option:-
1. Calculated Measure:= If(Table[Category]="CostPrice",[CostValue],If(Table1[category]="SellingPrice",[SalesValue],([SalesValue]-[CostValue]/[SalesValue])))
*[CostValue]:=Calculate(Sum(Table1[Amount]),Table1[Category]="CostPrice")
*[Sales Value]:=Calculate(Sum(Table1[Amount]),Table1[Category]="SellingPrice")
Tried this in both Calculated Column and Measure but not giving me required output.
View 4 Replies
View Related
Jun 19, 2015
I have the following scenario. I want to apply some calculations on different levels and then aggregate them up.
First measure calculates at Productgroup,color,store,size level
ProductGroup Color Store Size Amount Quantity
Measure:Amount*Quantity (ProductGroup, Color, Store, Size)
A Blue Store A L 100 6 600
A Red Store A S 150 4 600
A Green Store A M 160 7 1120
B Blue Store A L 300 3 900
[Code] ........
The other measure ignores color
ProductGroup Store Size Amount Quantity
Measure:Amount*Quantity (ProductGroup, Store, Size)
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
How should the two measure be defined:
Measure (ProductGroup,Color,Store,Size)
Measure (ProductGroup,Store,Size)
View 2 Replies
View Related
Oct 13, 2015
Can I force the following measure to be visible for all rows in a pivot table?
Sales Special Visibility:=IF(
HASONEVALUE(dimSalesCompanies[SalesCompany])
;IF(
VALUES(dimSalesCompanies[SalesCompany]) = "Sales"
;CALCULATE([Sales];ALL(dimSalesCompanies[SalesCompany]))
;[Sales]
)
;BLANK()
)
FYI, I also have other measures as well in the pivot table that I don't want to affect.
View 3 Replies
View Related
Aug 17, 2015
How can I apply "Min" formula under a "new measure" (calculated field) within a pivot table under Power pivot 2010?Can see that neither does it allow me to apply "min" formula directly "formula box" nor could find any other option.Intent formula: "=Min(1,sum(a:b))" this isn't allowed so all I can do is "=sum(a:b)".
View 3 Replies
View Related
May 4, 2015
I am trying to calculate the sum of gross sales over a rolling 6 month period in PowerPivot and don't even know how to start.
View 2 Replies
View Related
Jun 25, 2015
I'm working on a measure which I'd like to use in PowerView, my data model contains 2 things:
1) Employees' scores
2) Stores in which employees work in
I would like to be able to put the employee's score in comparison to the store average (which is the average of all the employee's scores in the store), and then provide every employee with an individual report on their scores against their store's score (by tiling the view in PowerView by Employee). To do this, my (first attempt) at a measure for the store average looks like the following:
Overall_Avg:=CALCULATE([Score], ALL(Employee[Name]))
Which gives me the score of the employee vs. all other employees across all stores. What I'd like to then achieve is to add an additional filter that filters only the scores for the store that the employee belongs to - which is where I'm stuck.
Here are some of my attempts at them
Store_Avg:=CALCULATE([Score], ALL(Employee[Name]), FILTER(Employee[Store] = Store[Store Name]))
returns an error for me. However, trying:
Store1_Avg:= CALCULATE([Score], ALL(Employee[Name]), FILTER(Employee[Store] = "Store 1"))
works perfectly, but doesn't give me the desired output, which is 1 measure that changes the store depending on which employee (and thus store) is being selected.
View 5 Replies
View Related
Oct 2, 2015
Any way to create a measure that filters the second column to mimic the behavior of only filtering for slicer for the Open Pool Date values as per the image below. Ultimately, I need to create a measure that only includes accounts that were opened 6 months prior to the month row context.
Trying to get the values in the second column to only include aging accounts 6 months prior instead of 12 months prior.
Trailing6Month Conversion:=CALCULATE([TOTAL LTD Converted Amount] ,Filter(Settlement700,Settlement700[OpenDatePool]>=RELATED(DimDate[Trailing6MonthsEnd])))
Trailing6Month LTD Conversion Amount:=CALCULATE([Trailing6Month Conversion],DATESINPERIOD(DimDate[FullDate],LASTDATE(DimDate[FullDate]),-6,Month))
View 17 Replies
View Related
Jul 20, 2015
I have a table with date in the following format:
orderID | dateOrdered | customerID
101 | 01/01/2015 | 1
102 | 02/02/2015 | 2
103 | 05/06/2015 | 3
I need to create a measure that tells me how many customer's placed an order in the previous 91 days of any given date, not necessarily today.
So, if you imagine a PivotTable which has a series of dates down the left hand side and 'totalNoCustomersLast60Days' as the only column, I need each day to tell me how many customers ordered in the x number of days.
For example if we say 60 days my table would look like the following:
Each row in this table would be telling me that "x number of customers placed orders within the period 60 days prior to,and including, the date on the left".
I have tried numerous ways of doing this and despite thinking it should be incredible simple it eludes me.
View 12 Replies
View Related
Jun 25, 2015
The following measure returns Month Sales: (blank)
Month Sales:=TotalMTD(Sum([Sales Amount]),'Date'[Full Date])
I have a relationship between ResellerSales-OrderDateKey and Date-DateKey
The relationship works fine when I use it in Access
View 3 Replies
View Related
Apr 22, 2015
I have a table in `PowerPivot` which contains the logged data of a traffic control camera mounted on a road. This table is filled the velocity and the number of vehicles that pass this camera during a specific time(e.g. 14:10 - 15:25). Now I want to know that how can I get the average velocity of cars for an specific hour and list them in a separate table with 24 rows(hour 0 - 23) where the second column of each row is the weighted average velocity of that hour?
A sample of my stat_table data is given below:
count vel hour
----- -------- ----
133 96.00237 15
117 91.45705 21
81 81.90521 6
2 84.29946 21
4 77.7841 18
1 140.8766 17
[Code] ....
In a separate `PowerPivot` table I have 24 rows and 2 columns(column1 is for hours and column 2 for weighted averages) and when I enter my formula, the whole rows get updated with the same number. My formula is:
=sumX(FILTER(stat_table, stat_table[hour]=[hour]), stat_table[count] * stat_table[vel])/sumX(FILTER(stat_table, stat_table[hour]=[hour]), stat_table[count])
View 3 Replies
View Related
Dec 1, 2015
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.
View 9 Replies
View Related
Jul 29, 2015
Say that I have employee data for 2010-2015. I have a pivot table that displays employee data for all the different months in 2015 I want each month to show all people who have been employed since the start of time until and including that month. So
for January 2015 it should show all people employed from January 2010 to January 2015.
for February 2015 it should show all people employed from January 2010 to February 2015.
for March 2015 it should show all people employed from January 2010 to March 2015.
How can I write a measure for this? I have a date table set up.
View 2 Replies
View Related
Nov 7, 2014
I want to count the rows in the Incident Table by using filters to limit the rows to be counted if they meet the below conditions. I know I need a logical test for each row of the incident table based on the apparatus table’s rows. But, I want to test for each row in the incident table, counting, but not returning a true or false in the overall measure.Something like look at each incident row, test for true or false and then count IF the statement is true. Then go to the next incident row and do the same. The aggregation would be the final count of “true” results.I tried this for MET objective:
=CALCULATE(COUNTROWS(incident),
apparatus[Incident Response Time] >-1 ||
apparatus[Incident Response Time] <320,
uv_901APP_TYPE[Description]="Engine",
uv_901INCIDENT[Top_Category]="Fire"
[code]....
View 13 Replies
View Related
Jun 5, 2015
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?
View 5 Replies
View Related
Oct 30, 2015
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.
View 2 Replies
View Related
Oct 13, 2015
My simplistic data model looks like this:
Table.1
Spread Product Sales Channel ..
2 x CB ..
2 y CB …
4 x GFR ..
4 y GFR ..
I need a column with Spread Mapping, which should look like this:
->There are more columns in data model.
-> Spread for GFR is wrong so it must be mapped to be able to compare with CB
I guess there should be calculated column formula to calculate Spread Mapping. How to do it?
View 6 Replies
View Related
Jul 21, 2015
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.
View 8 Replies
View Related
Aug 17, 2015
My actual data is a bit more complicated, and uses fact and dimension tables, but I simplified it here.
I'm trying to build a chart that will compare sales of a single store to regional and national sales. The measures look like this:
Sales = SUM(FactTable[Net Sales])
Regional Sales = CALCULATE([Sales], ALL(FactTable[Store Number]))
National Sales = CALCULATE([Sales], ALL(FactTable))
And it ends up looking like this:
Note that the Store Number is selected, but the Store Region is not, it's just the result of cross-filtering. Regional Sales incorrectly matches National Sales. If I then select the Region, the measures work:
I'm actually using VBA to change the Store Number slicer, as the end users don't want to select the region, then scroll through a list of store numbers. They just want to enter a store number and hit enter. I've tried a few things in DAX and VBA, and failed.
View 2 Replies
View Related
Nov 12, 2015
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.
View 3 Replies
View Related
Sep 29, 2015
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.
View 3 Replies
View Related
Jul 31, 2015
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.
[URL]
View 2 Replies
View Related
Jun 23, 2015
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:
=MinX(Filter(LookUpTable;LookupTable[from]<=MySourceTable[Day] && LookupTable[to]>=MySourceTable[Day]);LookUpTable[numericalColumn])
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.
View 5 Replies
View Related
Jul 27, 2015
We are trying to do some utilization calculations that need to factor in a given number of holiday hours per month.
I have a date dimension table (dimdate). Has a row for every day of every year (2006-2015)
I have a work entry fact table (timedetail). Has a row for every work entry. Each row has a worked date, and this column has a relationship to dimdate.
Our holidays fluctuate, and we offer floating holidays that our staff get to pick. So we cannot hard code which individual dates in dimdate as holidays. So what we have done is added a column to our dimdate table called HolidayHoursPerMonth.
This column will list the number of holiday hours available in the given month that the individual date happens to fall within, thus there are a lot of duplicates. Below is a brief example of dimdate. In the example below, there are 0 holiday hours for the month of June, and their are 8 holiday hours for the month of July.
DateKey MonthNumber HolidayHoursPerMonth
6/29/2015 6 0
6/30/2015 6 0
7/1/2015 7 8
7/2/2015 7 8
I have a pivot table create based of the fact table. I then have various date slicers from the dimension table (i.e. year, month). If I simply drag this column into the pivot table and summarize by MAX it works when you are sliced on a single month, but breaks if anything but a single month is sliced on.
I am trying to create a measure that calculates the amount of holiday hours based on the what's sliced, but only using a single value for each month. For example July should just be 8, not 8 x #of days in the month.
Listed below is how many hours per month. So if you were to slice on an entire year, the measure should equal 64. If you sliced on Jan, Feb and March, the measure should equal 12. If you were to slice nothing, thus including all 15 years in our dimdate table, the measure should equal 640 (10 years x 64 hours per year).
MonthNumberOfYear HolidayHoursPerMonth
1 8
2 4
3 0
4 0
5 8
6 0
7 8
8 0
9 8
10 4
11 16
12 8
View 3 Replies
View Related
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.
View 3 Replies
View Related
Sep 17, 2015
I cannot create a measure that returns results for dates that do not exist in the fact table despite the fact that the components included in the measure contain valid results for these same dates.Creature a measure that counts the number of days where the "stock qty" is below the "avg monthly sales qty for the last 12 months" (rolling measure).Here is the DAX code I have tried for the measure (note that filter explicitly refers to the date table (called Calendar) and not the fact table):
StkOutCnt:=CALCULATE (
COUNTROWS ( VALUES ( Calendar[DateKey] ) ),
FILTER (
Calendar,
[Stock qty] < [Avg Monthly Sales Qty L12M@SKU]
)
)
Below you can see the sub measures (circled in red) are giving results for all days in the calendar.Highlighted in yellow are dates for which the StkOutCnt measure is not returning a result. Having investigated these blank dates, I am pretty confident that they are dates for which there are no transactions in the fact table (weekends, public holidays etc...).why I am getting an "inner join" with my fact table dates despite the fact that this is not requested anywhere in the dax code and that the two sub measures are behaving normally?
View 6 Replies
View Related
May 13, 2015
In my cube there are two measures which are used in different calculations.Now I'm need to show in report if there any months in data when both or even another one of the measures is not updated (value = 0 or NULL).
how should I create the calculated measure for that?
I have tried in mgmt studio to plan this but I'm in a loop of errors.
View 4 Replies
View Related
Jun 9, 2015
I am trying to build various reports that compares data over time. I have one that measures Year Over Year % difference for number of incoming projects. I managed to do that easily by calculating the following
YTDProjects:=if(ISBLANK(SUM('TrendData'[Projects])),blank(),CALCULATE(SUM('TrendData '[Projects]),DATESYTD(CalendarDate[FullDate])))
PYProjects:=if(ISBLANK(SUM('TrendData'[Projects])),blank(),CALCULATE(sum('TrendData '[Projects]),SAMEPERIODLASTYEAR(DATESYTD(CalendarDate[FullDate]))))
YoYDifference:=[YTDProjects]-[PYProjects]
YoYPercProjects:=IF([PYProjects]=0, BLANK(), [YoYDifference]/[PYProjects])
Where Projects is the metric in question, TrendData is the table that contains project data and CalendateDate is the Date Table. But now I am trying to compare the same YTD projects data to number of projects that came in the last 6 months. How do our projects compare to average number of projects that came in last 6 month period.
I tried the the DATEADD function instead but got no luck and data came out wrong!
PrevProjects:=CALCULATE(SUM([Projects]),DATEADD(CalendarDate[FullDate],-1,QUARTER))
For some reason, this also returns blank in my model:
QTDProjects:=TOTALQTD(SUM('TrendData'[Projects]),CalendarDate[FullDate])
View 2 Replies
View Related
Sep 15, 2015
In SSAS, I want to add a calculated measure and set that measure as the Parent of existing measure. For example I have the measures as A and B from the fact table. Now i want to add a calculated measure as C and set this measure as Parent for measures A and B. How to do that in SSAS.
View 4 Replies
View Related
Aug 2, 2015
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
View 9 Replies
View Related
