Goals Reporting - Linking a Goal to a Subgoal (or a Sub-Sub-Goal...)
I've just started working with Goals and have a requirement to summarise some of the goal progress in a report for ease of use. Within the goals framework I have two overarching goals. One of them has six sub-sections (subgoals) and within those there are a number of subsequent subgoals (and sometimes a further subgoal under that). So typically it looks like this: Main Goal (Effectively a "master heading" goal). E.g. Implement BI Goals by 2023 (ID 1, for example) Subgoal under this. E.g. Prepare key objectives (ID 1-1, Parent ID 1) Subgoal under this. E.g. Identify 2022 milestones (ID 1-1-1, Parent ID 1-1) (Possibly) subgoal under this. E.g. Send user requirements by end of Q2 (ID1-1-1-1, Parent ID 1-1-1 etc) My end user is looking to see at a glance which "major" goals aren't yet completed (example in red), and their associated notes. So this means using a filter - probably a slicer - to identify the main goal, I've done that and it looks fine. What I need to do next though is create an associated matrix of subgoals under that parent ID with their status/notes etc. This is where I'm stuck - I'm unsure how to tell the matrix to reference the parent ID of the item selected in the slicer, and thus return the associated entries. Since Goals is quite tightly locked down I can access the PBIX file but I can't edit relationships or see the data tables (everything is connected to the dataset, but nothing is held locally), which leaves me largely relying on DAX. Has anyone managed to do anything similar with it yet? (Largely useless) example file: https://www.dropbox.com/s/2remz46phv6d0c6/Example%20Scorecard.pbix?dl=0
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.
Dax Calculation on power bi
file excel and pbix hai i wanna calculate total vehicle and total average base on dept like this on facility management (FM) i got total average = 59,11932 and total vehicle = 50 on finish good (FG) i got total average = 76,45089 and total vehicle = 28 but on my power bi, i canot make data like this this is my total vehicle divide by dept SumDistinctIfDifferent = VAR DistinctInternalIDs = VALUES( 'Table 2024-08-06 until 2024-08-27 (2)'[Total Vehicle 2] ) RETURN SUMX( DistinctInternalIDs, CALCULATE( DISTINCTCOUNT( 'Table 2024-08-06 until 2024-08-27 (2)'[internal_id]), ALLEXCEPT( 'Table 2024-08-06 until 2024-08-27 (2)', 'Table 2024-08-06 until 2024-08-27 (2)'[dept_id] ) ) ) how to calculate total vehicle and total average like my excel ?? total vehicle what i made Total Vehicle = DISTINCTCOUNT('Table 2024-08-06 until 2024-08-27 (2)'[internal_id]) DB sql source code : select distinct internal_id, type, status, unit, operational_date, time_start, time_finish, dept_id from vms_vhl_operational where operational_date between '2024-08-06' and '2024-08-27' and unit = 'MAIN'Measure not affected by table rows
Hello, everyone! I'm doing a measure that need to show me a value based on the total of a count. Depending on the total, it's going to show me diferent values, like: 1 to 24 = 47,00 25 to 30 = 55,00 31 to 40 = 60,00 More than 41 = 65,00 When I use this measure on a card it works perfectly, it shows me the exactly value I need, like the image below: But, when I try to use this on a TABLE it breaks the value per row. On the total it show correctly how the value is supposed to be, but on the rows it shows the minimum value. There's a way to make the measure show me the value in the total cell on the rows? The measure I using is this one: VALOR_COMISSAO = VAR qtd_benef = CALCULATE( DISTINCTCOUNT('power_bi gestao_contratos'[codigo_pessoa]), FILTER('power_bi gestao_contratos','power_bi gestao_contratos'[indica_plano_ant] = "N"), 'power_bi gestao_contratos','power_bi gestao_contratos'[SITUAÇÃO FATURA] = "PAGO", KEEPFILTERS('Tb_Calendário'[Competencia]), REMOVEFILTERS('Tb_Calendário'[Ano])) VAR valor_comissao = CALCULATE( MAXA('power_bi comercial_val_comiss_consult'[valor]), FILTER('power_bi comercial_val_comiss_consult', 'power_bi comercial_val_comiss_consult'[vidas_inicial] <= qtd_benef), GROUPBY('power_bi gestao_contratos','power_bi gestao_contratos'[nome_vendedor]), KEEPFILTERS('Tb_Calendário'[Competencia]), REMOVEFILTERS('Tb_Calendário'[Ano])) RETURN valor_comissao Basically this measure has two steps separated in two variables: qtd_benef: this measure count the values that will be used in the next step. valor_comissao: the result of "qtd_benef" is the value used on the FILTER command to get the value of MAXA. the QTD_BENEF it the (34) and VALOR_COMISSAO is (60) that appears on the first image. Thanks in advance, Daniel.
Calculate values for the previous trading day when weekends are excluded
Hi. I want to calculate a measure to see required margin for the previous trading day. The dates I have in the table include all dates. I have tried tried a function as shown below. It doesnt show required margin for the previous trading day for weekends and holidays. On the start of the week (monday), it usually shows 0. On the report date it should show the required margin on last friday. How can I make a measure for required margin from the last tradingday when weekends and holidays are excluded?
DAX in building Rolling 4 Week Average
Hi, I need help in building 4 week rolling average for "Net Change in Price and Cost" with below sample data Change in Price, Change in Cost and Net Change in Price and Cost are all 3 measures. Net Change in Price and Cost = Change in Price - Change in Cost I do have separate date table and the above data shows weekly data and my report has Invoice Date as Filter. I need to calculate rolling 4 week average, here the tweak is that for first week start date 12/07 rolling average should be same while for next 12/14 it should (-4.80% + 0.18%)/2 = -2.31% for 12/21 it should be (-4.80% + 0.18% - 0.04%)/3 = -1.55% for 12/28 it should be (-4.80% + 0.18% - 0.04% -0.65%)/4 = -1.33% for 01/04 it should be (0.18% - 0.04% -0.65% +1.03%)/4 = 0.13% as shown below I have created below DAX but its not working as expected Net Change in Price and Cost 4 Wk Rolling Avg = var start_day = MIN('Date'[Week Start Date])-21 var end_day = MAX('Date'[Week Start Date]) return CALCULATE([Net Change in Price and Cost], DATESBETWEEN('Date'[Week Start Date],start_day,end_day), REMOVEFILTERS('Date'[Week Start Date]))/CALCULATE(DISTINCTCOUNT('Date'[Week Start Date]),DATESBETWEEN('Date'[Week Start Date],start_day,end_day),REMOVEFILTERS('Date'[Week Start Date])) Can someone help me on this??
Issues with comparing data using date ranges
I am new to PowerBI and defenitely not an expert or developer. I was able to create a report based on an excel file which contains server names, CPU - Avail memory and % of avail memory, date and time. I need to be able to compare utilization between Feb 2 - 16 and then from Feb 20 - March 1 to show any utilization spikes after adding a new customer. I have tried using a Measure and an If statement with no luck. The excel sheet contains: server name date time cpu avail mem % of avail mem it has an hour entry (24 in one day). Any advice? Thank you in advance.Split Text into rows using DAX
Hi power BI expert, I need to split into rows using DAX instead of using Power Query due to performance (the data is too large). For info, previously i was using Power Query but I'm keep getting error messages saying about performance. Below is my sample data: There is some calculation that i need to do once names splitted into rows. Really need anyone help to achieve this Thank youMeasurements in a single column with 9 values, Power BI
Hello community. I am trying to show the values of nine measurements [Ai=1..,9 Variable] in a single column, as show: but I get an error, in line 14, with the variable posic, after using the following formula: 1 MOSTRAR_ACTIV = SUMX(ACTIVIDADES, 2 VAR _Indx =[POSICION] 3 VAR posic = 4 IF(_Indx = 1, [A1 Variable], 5 IF(_Indx = 2, [A2 Variable], 6 IF(_Indx = 3, [A3 Variable], 7 IF(_Indx = 4, [A4 Variable], 8 IF(_Indx = 5, [A5 Variable], 9 IF(_Indx = 6, [A6 Variable], 10 IF(_Indx = 7, [A7 Variable], 11 IF(_Indx = 8, [A8 Variable], 12 IF(_Indx = 9, [A9 Variable], 13 SWITCH( TRUE(), 14 IF(_Indx<=9, posic ))))))))))) I tried with another formula: 1 MOSTRAR_ACTIV = 2 SWITCH ( 3 SELECTEDVALUE(ACTIVIDADES[POSICION]), 4 1, [A1 Variable], 5 2, [A2 Variable], 6 3, [A3 Variable], 7 4, [A4 Variable], 8 5, [A5 Variable], 9 6, [A6 Variable], 10 7, [A7 Variable], 11 8, [A8 Variable], 12 9, [A9 Variable], 13 150 14 ) No errors, but show only the value 150 of line 13. Could someone help me determine what is wrong? Thanks, before handsCalculate Date (Gantt Chart)
Hello, i'm making a Gantt chart where i have several projects and these projects have several tasks but I would like only the start and end date to be displayed for 1 project, for example in this project i have 4 tasks So when they are added to the diagram they apper like this And i just want a line on the Gant chart with the start on August 8 and the end on Agust 17 and not 4 lines. What can i do?
