Help Calculating Average Excluding 2 Conditions
I have an existing DAX measure which gets average of completed fields in the "Master AE" table (there's 35 required fields): [EPIC %] = CALCULATE( DIVIDE([EPIC Points], COUNTROWS('Master AE')*35)) The current measure gives this output on my KPI cards, which is correct: However, I need to modify it to get a new average that EXCLUDES any rows where Master AE [EPIC Points Count] is <=17 AND the Master AE [Quarter] is > 2. Or basically, exclude any results from EPIC % measure result <= 49% AND Quarter > 2. The desired result average should be 40.28% (only 10 values would be averaged per the conditions) Any idea how I can do this? Here is the [EPIC points] measure which sums all the points assigned when blanks completed: Appreciate any help!
Average based on Filter
I am trying to get the average of project completions per week and then when I am changing the filter as what weeks I am seeing that the average would be tied into this filter. It is currently do the completion average for all the data which is this. Average = CALCULATE(COUNT('CP Jobs'[Project]),ALL('CP Jobs'))/CALCULATE(DISTINCTCOUNT('CP Jobs'[Year-Wk]),ALL('CP Jobs'))
Distinct Count ignoring one column plus calculate average
Hello, all! I have the following table And im trying to get a simple DISTINCT COUNT[ID] but its not working because the column Type has two different values (B and E). If i use a mesure, it works BUT im trying to use a Column because after i get the distinct count, ill need get the average. Like the example below: In short, i need to get the average of distinct count[id]. Ive tried create a column with CALCULATE(DISTINCTCOUNT(Consult[id]) / DISTINCTCOUNT(Consult[date].[Month]), ALLSELECTED(Consult[date].[Day])) But its not working because its counting the same ID twice when the ID has the B and E type. If there is a way to get the column average that i need using a measure, ill be glad to learn how. Thanks!Average Between Three Dates Versus Main Date
I am creating a dashboard to show the days between(Lag) the latest of three different date columns versus the main date(Physical Date) column. Now I need to find the average days between those three different date columns versus the main date(Physcial Date) column and present it in a visual like a bar or line chart. Example: Product Order was placed and shipped(Physical Date)and it goes into either three different date column flows: 1) Submission Date(Meaning it is the normal price and it goes straight to invoicing) 2) Price Approved Date(The product has a special price so that price needs to get approved before it goes to invoicing) 3) Terms Approved Date(The product price has different terms than normal so that needs to be approved) They want to know how many days it took to move between main date(Physical Date) to Price approved date, or between Main Date (Physical Dat)e to submission date etc. Currently I am using a calulcated column to find the last date of the three dates compared to the main date(Physical Date) in days. DaysBetween Lag Column = VAR _pDate = SELECTEDVALUE('TABLE1'[Physical Date]) VAR _paDate = SELECTEDVALUE(Table1[PriceApproved.date]) VAR _taDate = SELECTEDVALUE(Table1[Terms Approved Date]) VAR _sDate = SELECTEDVALUE('Table1'[Submission.date]) VAR _maxDate = MAX(MAX(_paDate, _taDate), _sDate) RETURN DATEDIFF(_pDate, _maxDate, DAY) That column displays in a table like this: They are wanting to group by the latter of the three dates (Price Approved Date, Submission Date, Terms Approved Date) versus physical Date and then get the average of that. The average part is what I am needing help with. Any suggestions please? Data Table is just sample data. Please Note: The three dates (Submission Date, Price Approved Date and Terms Approved Date) will not always have a value depending on what "flow" the item went into it after it was shipped. Dangar332 or anyone else...Any suggestions Please? Thank you in advance! Product ID Physical Date Submission Date Price Approved Date Terms Approved Date 24567 1/4/2024 1/7/2024 676742 1/6/2024 1/10/2024 42516 1/15/2024 1/26/2024
Trying to get the Average of the count of items
We have a table. it has many values in but some of the values are these: DCP-Data Service Date CODE Shift 1/2/2023 99154 Afternoon 1/2/2023 99154 Evening 1/3/2023 99154 Afternoon 1/3/2023 99154 Afternoon 1/2/2023 99154 Evening 1/2/2023 99202 Afternoon What we need to know is the average number of times this particular code has been used over a period of time. We may filter by shift but we may also filter by date or pay period (a month at a time) I created this measure based on some research: AvgCode = AVERAGEX(VALUES('DCP-Data'[SERVICE DATE]), calculate(count('DCP-Data'[CODE]))) What ended up happening, though, is for the given period we looked at (a month in this case) it only selected the values in the month and averaged the times it was used only. So, above example, 99154 was used on two days five times that was 2.5 times on average. It ignored the line with 99202 in it. What I really need is out of every day in that month (1/1 - 1/30) how many times on average was that used? In this example it would be < 1 time. If I'm slicing on the shift name, then it wold adjust accordingly to tell me how many times in the month just for that shift. I can't figure out how to do that. I tried using just the table and not "SERVICE DATE" in the first values but that messed things up even more. Can anyone think of what can be done?
How to get average of monthly values for per year per category
My dataset looks like the below, I have created a matrix visual as below, The values in the matrix visuals are distinctcount of the ids. Now, I want to calculate the average value of the red boxes (i.e.) the average of monthly values for each year for each category. How can I get this in DAX?calculate cycle times, get an average excluding top 15%
In a manufacturing setting I have a pick started and pick completed time/date stamps, i have subtracted them from one another to get the duration or cycle time. I am trying to build a report to get the average which i did using a measure however my data is skewed due to some outlier times and i would like to exclude the top 15% without removing the data. I know i can do it with if functions within excel but i am racking my brain with DAX.
Tricky Situation with SUM
Here is the Data I am using: Type Spent Returns Apple 100 Orange 50 200 Banana 25 150 Pear 500 Kiwi If I use ROI = sum(Data[Returns]) / sum(Data[Spent]) I get the following Information on Data: the spent and returned data is filled in by the user and will remain blank until then. So will have to create DAX measures that will assign 0 where there is a blank. However, if we do that, we get Infinity and NaN for Pear or Apple. So we have to HardCode such incidents to 0. Questions 1) I also want to show the Kiwi line item as well and assign 0 to the ROI column for Kiwi. 2) How to deal with Infinity, show 0 instead of Infinity. 3) Result in either 1 of the shown expected output will work Any suggestions, tips, and tricks are welcome. I want to get the following output along with totals at the end for the first two columns and the average for the third column, shown below: Expected Output Type Spent Returns ROI Apple 100 Orange 50 200 4 Banana 25 150 6 Pear 500 Kiwi Total 175 850 5 OR Type Spent Returns ROI Apple 100 0 Orange 50 200 4 Banana 25 150 6 Pear 500 0 Kiwi 0 Total 175 850 5
