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.
Measure Function for that Acts as Show Value As Percent of X
Hi! I think it would be benefitial if there was a way to create a measure that acts the same as using the "Show value as > Percent of Grant Total, Column Total, Row Total" options that are provided when adding a measure to a table. Use the ALL, ALLSELECTED, and ALLEXCEPT do not always work especially when the heiracry changes or filters are applied but the Percent of Total is always correct. Being able to put it into a measure would also enable users to put the measure into "Field Values" and would make it a lot more versitile.
