Bad calculated ratio at lower level matrix
Hello all, I have created this mesure and i'm triying to use it in a matrix, ratio by etablissement and total ratio are right. My problem is that the ratio by department is not calculated in the rigth way. In my Matrix "etablissement" is the first level and "departement" my second level. See in the picture what I get (left) by département and what I should obtain (right). And here my measure, what should I do to correct this? My two table have a relationship many to many Thanks in advance Prorata etablissement et departement = VAR Etablissement = SELECTEDVALUE('Plan'[ETABLISSEMENT CONSO]) VAR Departement = SELECTEDVALUE('Plan'[DEPARTEMENT]) VAR CurrentMonth = "202405" -- Remplacez cette valeur par le mois courant si nécessaire VAR Numerator = CALCULATE( SUM('Plan '[Nb Heures Catalogue]), ALLSELECTED(FTEHC[AnnesMois]) ) VAR DenominatorPerEtablissement = CALCULATE( DISTINCTCOUNT(FTEHC[ID]), FILTER('FTEHC', FTEHC[AnnesMois] = CurrentMonth && 'FTEHC'[Position lieu_2] = Etablissement) ) VAR DenominatorPerDepartement = CALCULATE( DISTINCTCOUNT(FTEHC[ID]), FILTER('FTEHC', FTEHC[AnnesMois] = CurrentMonth && 'FTEHC'[Departement OM] = Departement) ) VAR DenominatorTotal = CALCULATE( DISTINCTCOUNT(FTEHC[ID]), FILTER('FTEHC', FTEHC[AnnesMois] = CurrentMonth) ) RETURN SWITCH( TRUE(), ISINSCOPE('Plan'[ETABLISSEMENT CONSO]), DIVIDE(Numerator, DenominatorPerEtablissement), ISINSCOPE('Plan'[DEPARTEMENT]), DIVIDE(Numerator, DenominatorPerDepartement), DIVIDE(Numerator, DenominatorTotal) )
Subtotal and total not showing
Hi all, I get values in the rows, but no values in the subtotal or total. I need the subtotals and total to be the average of the values. I have the following DAX: Volume compliance (on archetype level) = VAR NoOfMetrics = 2 VAR VolumeCon = AVERAGEX('SiteSnaphot Volume', ([CBM - Inbound (%)] + [CBM - outbound (%)]) / NoOfMetrics) VAR VolumeDecon = AVERAGEX('SiteSnaphot Volume', ([Cartons - Inbound (%)] + [Cartons - outbound (%)]) / NoOfMetrics) VAR VolumeFul = AVERAGEX('SiteSnaphot Volume', ([Units - Inbound (%)] + [Units - outbound (%)]) / NoOfMetrics) RETURN SWITCH(SELECTEDVALUE('SiteSnaphot Volume'[ArcheType]), "Consolidation", VolumeCon, "Deconsolidation", VolumeDecon, "Fulfilment", VolumeFul) Furthermore, if you have a solution on how I can make the VolumeCon etc. more dynamic that dividing with 2. This is a snip of the subtotal (upper white cell with no value) and row value (blue cell with 100%). Furthermore, the [CBM - Inbound (%)] etc. are measures that I have created. Hope you can help me. Viktor
Filter context and Matrix Visual - Weekly average for selected period
Hi, I am fairly new to power BI and have a problem I cannot solve, Ihave searched the boards and can't find a solution. I would like to display the weekly sales average for a selected period in a matrix visual with the weekending days for the selected period as the columns. I have a calculated table that return a dynamic date period (essentially last 4, 8, 12 weeks) and filters the Date table, which in turn is directly related to the Sales table. So I can offer the user the option to view only the last 4, 8, 12 weeks of Sales. I have a requirement to return a matrix with product code as rows and weekending dates as columns that show total sales, and averages sales for the period selected. I need a measure that retains the time period filters for the average but removes the calendar filters on the visual so that each week column has the same average sales. Current attempt (returns the average for the week column - which is the same as the sales value obviously) is: Currently my Weekly Sales for the Selected Period is Calculated like this: Sales Period Selected = VAR MinWeek = CALCULATE ( MINX ( FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = MIN ( 'Time Period Selector'[Date] ) ), 'Calendar'[WeekEnding] ), ALLSELECTED ( 'Time Period Selector' ) ) VAR MaxWeek = CALCULATE ( MAXX ( FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = MAX ( 'Time Period Selector'[Date] ) ), 'Calendar'[WeekEnding] ), ALLSELECTED ( 'Time Period Selector' ) ) VAR FirstForecastWeek = CALCULATE ( MAXX ( FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[IsFutureDay] = FALSE () && 'Calendar'[IsCompletedWeek] = TRUE () ), ( 'Calendar'[WeekEnding] ) ) ) RETURN CALCULATE ( [Sales Switch], FILTER ( Sales, Sales[Tax date] > MinWeek && Sales[Tax date] <= FirstForecastWeek ) ) And the weekly average is like this: Averagy Weekly Sales = AVERAGEX( SUMMARIZE( ALLSELECTED('Calendar'), 'Calendar'[WeekEnding] ), [Sales Period Selected] ) Which becasue of the Filter Context in the table gives the same value for the week as the sales (average of weekly sales over 1 week = sum of sales) If I remove filters like this AVERAGEX( SUMMARIZE( ALLSELECTED('Calendar'), 'Calendar'[WeekEnding] ), [Sales Period Selected] ), REMOVEFILTERS('Calendar') ) I get the right value (So the correct average over the number of weeks in the selected period) BUT... the martix visual then retunrs ALL the weeks in the calendar table Red weeks NOT in period, Yellow in period Please help
Average Last 6 months accumulated
Hello dear community, I come to you so that you can help me with this DAX calculation. I need a Calculated Column to show me: the average of the last 6 months accumulated for each collaborator (in case the collaborator appears> = 6 times in the year); or in the event that the employee in the current year period appears less than 6 months, showing the average based on the number of months that the employee has been in the company in the current period, for example: Year Filter: 2021 Current month: May2021 (5) Seniority of the collaborator to May2021: 3 months (that is, the collaborator appears 3 times until May, if in June it becomes active, the collaborator when we are in June then it will appear 4 times, and so on) So the average will be = (Mar + Apr + May) / 3 Also consider that I already have a calendar table in my Power BI. Here I have an example sample of what I want exactly (column "Average Last 6 months), you can download the file here: I also leave the extract here: I read them, I hope they can help me as soon as possible. and thank you very much in advance. Nestor ReyesHelp Required to Calc 2 averages, of different time frames from a Matrix in PBI
Hi All, please can you assist me with some help in DAX, Below I have a very basic data set. I need to either add 2 x Cac Cols or Measures of: 1. The average of the last 3 days 2. The average of last day 10 to last day 4 The DAX I'm trying is below: but stumped honestly: Last 3 days = CALCULATE(AVERAGEX(VALUES(Query1[4G Data Volume_STD(MB)]), DATESINPERIOD(Query1[Time],LASTDATE(Query1[Time]), -3, DAY))) I need the last 2 columns, colour coded of what the averages must be of This is my dataset:
