measure average age per order
Hi, in my powerbi report I have this measure called "# Open Orders EOP" where I measure how many open orders we have per week. Now I also want to add the average age per open order. The result should look like column E from the screenshot below. Column A, B, C and D is what I have in my powerbi report, but I want to add column E (the individual values are maybe not correct). measure1 OpenOrdersWoW = VAR MinDate = MIN ( 'dim_date'[Date] ) VAR MaxDate = MAX ( 'dim_date'[Date] ) VAR Result = CALCULATE ( COUNTROWS ( tableA ), tableA[CreatedDate] <= MaxDate, OR( tableA[ClosedDate] > MinDate, ISBLANK(tableA[ClosedDate]) ), REMOVEFILTERS ( 'dim_date' ) ) RETURN Result measure2 (column D) # Open Orders EOP = CALCULATE ( [OpenOrdersWoW], LASTDATE ( 'dim_date'[Date] ) )
Very high total table results, does not average.
HELP PLEASE! Hello, I have a problem that I have been looking for the solution for several days but I can't find it. I would be very grateful if someone could help me, because it is something that I will have to use a lot and I have no way to solve it. I have a table with data, in which when I make the comparison to see the % that we have gone up or down with respect to last year gives me very high results. When I do the comparison by date I get the total result very high, but when I do it by week and month I get all the results very high, both the total and the partial results. What I want is that the total result (like the weekly and monthly ones) gives me the average of all the results that compose it, and it is impossible that these very high numbers that I get are averages. I leave here below the images in which you can see my case. I am also going to leave the formula that I am using for it, since I believe that the solution may be that the dax formula that I am using is wrong. The formula is the following: R_LYDay(B) = VAR _actual_total = SUMX( VALUES(calendar_table[Date]), SUM(R) ) VAR _LY_total = SUMX( VALUES(calendar_table[Date]), CALCULATE( SUM(R), DATEADD(calendar_table[Date], -364, DAY) ) ) RETURN DIVIDE(_actual_total - _LY_total, _LY_total, 0) When I put “R” is where the main measurement is, but for confidentiality I prefer not to share. I would be very grateful if someone could give me a solution. Thank you very much in advance.
Count rows of the values of a measure
Hi All, I tried looking at various posts here to see if someone had similiar ask, but coudnt find one that really fits, but if its something i missed please refer me to the post happy to review it. Herewith my query: I have created a Measure as follows: OffMarc_Cost = CALCULATE(SUM('Hana Order Acutal Cost'[Cost]),'Hana Order Acutal Cost'[Cost Element] <> "0000555090",'Hana Equipment Additional Data'[Equipment F/L] = "1200-MO-0215-M020-001") OnMarc_Cost = CALCULATE(SUM('Hana Order Acutal Cost'[Cost]),'Hana Order Acutal Cost'[Cost Element] = "0000555090",'Hana Equipment Additional Data'[Equipment F/L] = "1200-MO-0215-M020-001") This sum all the cost of a specific piece of equipment that falls into these specific filter. I want to be able to create a rowcount from the result of these measure as per below example. How will i create this in a measure, as i will use these totals for a month then in a calculation.
create area chart with green and red colors to indicate profit and loss
I have created area chart that that shows profit and loss % over time. I would like to show the loss (negative part) in red color and the profit ( positive part) in geen color. i created 2 measures one for profit % and one for loss % positive p&l = var pl=[p&l %] var result= IF(pl>0 , pl,0) return result negative p&l = var pl=[p&l %] var result= IF(pl<0 , pl,0) return result when i added the measures in the chart it looked like: any idea on how to create the area chart with green/red colors instead of 1 color
Count number of times something happens in measure
Hi all, I already had a ton of help from the community on my question, but I got stuck again (see this question for the earlier help: Solved: Count number of times something happens in measure - Microsoft Fabric Community) I would like to count the number of times per date per part that the percentage #good/#done is below 75% AND I would like to count the number of time per date per part that the percentage (#planned - #done)/#planned is above 10%, and do this in a measure. When either the #good/#done is below 75% or (#planned - #done)/#planned is above 10% I call this a black day. In another measure I would like to calculate the same, however I would like to count the number of times per date per part that the percentage #good/#done is below 85% AND I would like to count the number of time per date per part that the percentage (#planned - #done)/#planned is above 5%, and do this in a measure. When either the #good/#done is below 85% or (#planned - #done)/#planned is above 5% I call this a red day. However, when something is already categorized as a black day, it cannot be a red day anymore. E.g. #good/#done: - 1 jan 2024 for part X: (55+55)/(60+70) = 84,6% - 2 jan 2024 for part X: (55+55)/(55+100) = 71,0% - 3 jan 2024 for part X: (55+55)/(60+60) = 91,7% - 1 feb 2024 for part X: (62+60)/(60+70) = 93,8% E.g. (#planned - #done)/#planned: - 1 jan 2024 for part X: (60+70 - (60+70)) / (60+70) = 0% - 2 jan 2024 for part X: ((80+90) - (55+100)) / (80+90) = 8,8% - 3 jan 2024 for part X: ((60+75) - (60+60)) / (60+75) = 11,1% - 1 feb 2024 for part X: ((60+70) - (60+70)) / (60+70) = 0% Therefore the days will be qualified as follows: - 1 jan 2024 for part X: red OR no qualification, so red - 2 jan 2024 for part X: black or red, so black - 3 jan 2024 for part X: no qualification or black, so black - 1 feb 2024 for part X: no qualification or no qualification, so do not count Eventually I would like to count the number of times per month that this happens, and also be able to show exactly on which day this happened. For example for part X: Month Black day Red day Jan 2024 2 1 Feb 2024 2 0 March 2024 (just as example added) 5 1 And if I then want to show it per day for january for example (and then only show the rows with a red or black day): Day Black day Red day 1 jan 2024 1 2 jan 2024 1 3 jan 2024 1 2 feb 2024 1 3 feb 2024 1 What I did so far is: Create a measure to calculate #good/#done Percentage punct = VAR _currentDatePunc = MAX ( 'Table'[Date] ) VAR _currentPartPunc = MAX ( 'Table'[Part] ) VAR _SumGoodPunc = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _currentDatePunc && 'Table'[Part] = _currentPartPunc ), 'Table'[#good] ) VAR _SumDonePunc = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _currentDatePunc && 'Table'[Part] = _currentPartPunc ), 'Table'[#done] ) RETURN _SumGoodPunc / _SumDonePunc Create a measure to calculate (#planned - #done)/#planned Percentage Uitv = VAR _currentDateUitv = MAX ( 'Table'[Date] ) VAR _currentPartUitv = MAX ( 'Table'[Part] ) VAR _SumGeplandUitv = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _currentDateUitv && 'Table'[Part] = _currentPartUitv ), 'Table'[#planned] ) VAR _SumDoneUitv = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _currentDateUitv && 'Table'[Part] = _currentPartUitv ), 'Table'[#done] ) RETURN (_SumGeplandUitv - _SumDoneUitv) / _SumGeplandUitv Create a measure for #good/#done red days: MEASURE Punc RD = VAR _VirtualTbalePunc = SUMMARIZE ( 'Table', 'Table'[Date], 'Table'[Part], "_percentage", 'Table'[Percentage punct] ) RETURN COUNTX ( FILTER ( _VirtualTbalePunc, 'Table'[Percentage punct] < 0.85 && 'Table'[Percentage punct] >= 0.75), [_percentage] ) Create a measure for (#planned - #done)/#planned red days: MEASURE Uitv RD = VAR _VirtualTbalePunc = SUMMARIZE ( 'Table', 'Table'[Date], 'Table'[Part], "_percentage", 'Table'[Percentage Uitv] ) RETURN COUNTX ( FILTER ( _VirtualTbalePunc, 'Table'[Percentage Uitv] > 0.05 && 'Table'[Percentage Uitv] <= 0.1), [_percentage] ) But then I am stuck, so hope anybody can help me further :). Thanks in advance.Count number of times something happens in measure
I would like to count the number of times per date per part that the percentage #good/#done is below 85%, and do this in a measure. E.g. - 1 jan 2024 for part X: (55+55)/(60+70) = 84,6% - 2 jan 2024 for part X: (55+55)/(55+75) = 84,6% - 3 jan 2024 for part X: (55+55)/(60+60) = 91,7% Eventually I would like to count the number of times per month that this happens, and also be able to show exactly on which day this happened. For example: Month Number Jan 2024 2 Feb 2024 5 March 2024 3 And if I then want to show it per day for january for example (and then only show these two rows): Day Number 1 jan 2024 1 2 jan 2024 1 Hope anybody can help me :).
Issues with calculating total score when multiple periods are selected.
Hello, I have a complex power bi report that I use for team member preformance in my department. We have a total preformance metric that I have calculated using a measure. The main report time filter is using specific period numbers. I had an incorrect understanding of how measures and filters worked. And was under the assumption that the code was calculating averages. This is not the case. When a single period is selected, the calculations are correct. But if multiple periods are selected, the calculations are not. I need the measure to average to total score for any of the selected periods. But I have no idea how to do this. Below is a screenshot for reference. All the calculations for Analyst Total Metric Calc (far left columns) are correct. But the total needs to be an average over the periods selected (so (1.95 + 2.70 + 3.30)/3) The correct value for the above table should be 2.65. Trying to write an average measure returns the same value. Analyst NEW Total Metric Calc = AVERAGEX('z_Score-TotalMetrics', [Analyst Total Metric Calc]) I tried to calculate something that would count the number of distinct periods and then divide the total metric by those periods. But it was doing the total metric calculation THEN dividing. I thought maybe I needed to make a calculated table where these values can live so I can average them. But I cannot for the life of me figure it out. Also, these reports need to refresh automatically, and I've had some errors where refreshes fail using calculated tables. I think the table would need to be Person Period Number Total Score Person A 2023-11 1.95 Person A 2023-12 2.7 Person A 2023-13 3.3 Person B 2023-11 3.3 Person B 2023-12 3.85 Person B 2023-13 3.6 So I could then do AVERAGE (Total Score). But I'm at my wits end. I was trying to give an example/sample report but it's super complicated and too big to attach here. Please let me know if there's more information that's needed. Below are the measures and tables involved. My dates table has the period number and period index. Analyst Total Metric Calc = ---Correct as long as only one period is selected in the filter VAR _QA = [QA # Metric] * [QA Weighted %] VAR _L1Resolve = [L1 Resolve # Metric]*[L! Resolve Weighted %] VAR _TTR =[L1 Time To Resolve # Metric] * [TTR Weighted %] VAR _KBA = [KBA Use % # Metric]*[KBA Weighted %] Var _Handle = [Handle Time # Metric]*[Handle Time Weighted %] VAR _Attendance = [Attendance # Metric]*[Attendance Weighted %] VAR _Adherence = [Adherence # Metric]*[Adherence Weighted %] VAR _MinIndex = MIN('Filter-Week Number'[Period Index]) VAR _TotalMetricCalc = CALCULATE(IF(_MinIndex>31, _QA + _L1Resolve + _TTR + _KBA + _Handle + _Adherence + _Attendance, DIVIDE([QA # Metric]+[Adherence # Metric]+ [Attendance # Metric]+[L1 Resolve # Metric]+[L1 Time To Resolve # Metric]+[Handle Time # Metric],6 ))) RETURN ROUND(_TotalMetricCalc,2) Period Slicer is just the Period Number with no additonal filters Below is the table for the selected range in the above screenshot. Period Number Period Start Date Period End Date Fiscal Year Fiscal Year Start date Fiscal Year End Date Period Index Year Index 2023-11 11/5/23 0:00 12/2/23 23:59 2023 1/29/23 0:00 1/27/24 0:00 36 2 2023-12 12/3/23 0:00 12/30/23 23:59 2023 1/29/23 0:00 1/27/24 0:00 37 2 2023-13 12/31/23 0:00 2/3/24 23:59 2023 1/29/23 0:00 1/27/24 0:00 38 2
