User Profile
corange
Post Patron
Joined 7 years ago
User Widgets
Contributions
Pareto Analysis & Measures
Hi everyone, I am after some recommmendations / directions. I have created a Pareto Analysis on POWER BI using the following measures: Total_Amount = SUMX(JobDetails, JobDetails[ServiceCat]) Rank_All = RANKX(ALL(JobDetails[ServiceCat]), [Total_Amount]) Pareto_Value = SUMX(TOPN([Rank_All], ALL(JobDetails[ServiceCat]), [Total_Amount]), [Total_Amount]) Amount_All = CALCULATE([Total_Amount], ALL(JobDetails[ServiceCat]) Pareto_% = DIVIDE([Pareto_Value], [Amount_All], BLANK()) It works well however, when I add other measures in the table (e.g. daily averages) or a field from the table, the ranking goes wrong and doesnt work anymore. Do you know what needs to be done to avoid this? Example of measures I am trying to add to the table are: Daily Avg Job= DIVIDE(SUM(Jobs_Details'[Is Countable]),SUM(vw_Calendar_BI[IsTradingDay]),0) Avg Job Value =DIVIDE(SUM(Jobs_Details'[TOTAL_CHARGE]), SUM(Jobs_Details'[Countable]),0) Thank you.RANKX breaks when adding another measure / fields
Hi everyone, I am trying to rank clients based on profit. I have used this formula: RANK = RANKX(ALL('2 - JobsDetails'[CLIENT_CODE]),CALCULATE([Profit],ALLEXCEPT('2 - JobsDetails','2 - JobsDetails'[CLIENT_CODE]))) and also tried this one: RANK= RANKX(ALLSELECTED('2 -JobsDetails'[CLIENT_CODE]),CALCULATE(SUM('2 - JobsDetails'[TOTAL_CHARGE])-SUM('2 - JobsDetails'[TOTAL_PAY])),,DESC,SKIP) However when I tried adding other measure / field in my visual, the rank breaks. I have double 2s ranking or 17s... Could someone let me know what I am missing in my formula or propose one that will only rank the profit while letting me add other measures to the visual so I can present my data. Thanks in advance.Cumulative not carrying over when no purchase
Hi everyone, I am struggling with one of my report. I don't understand why it is not showing up the right results. I need to calculate points accrued for a client. Points are accrued each month and carried over if any left. For this, I have used a cumulative measure as follows: Cumulative Points = IF(ISBLANK(SUMX(test1,test1[Nber of Job])), BLANK(), CALCULATE(SUMX(test1,test1[Nber of Job]),FILTER(all('Calendar'),'Calendar'[SingleDate]<=max('Calendar'[SingleDate])))) Then I use the following to know how many vouchers I need to give away (1 voucher for every 150 points). Tickets Current = QUOTIENT([Cumulative Points],150) To know exactly how many voucher I need to give for any given month, I deduct the number of voucher they have received the previous month as I use a cumulative measure to make sure I take into consideration carry over points. Ticket to get = ([Tickets Current]-[Tickets Last Month]) **Tickets last month = (CALCULATE([Tickets Current], DATEADD('Calendar'[SingleDate], -1, MONTH))) The below are the results I obtained: I don't understand why the cumulative is not showing up for Jan. The client didnt make any purchase that month but I thought it will still carry over? Also I don't understand the -18 in Jan or 18 in March. With the cumulative results adding up for January even thought the client didnt purchase, will give me the right results for the voucher. Can someone please help with the formula to fix this? You can access the pbix file here if that can be useful: https://1drv.ms/u/s!AiS7XVNuQsBCfLR3YfEnr4wJxEo?e=fM0NE4 Thank you.Calculate percentage total / Calendar
Hi everyone, I am stuck with a metric. I am trying to calculate a % total for each period in a month. A month has two periods and I want to be able to know what is the percentage of A and B for that period using a graph as a visual. When I use a table, I obtained the desired result but when I try to graph it, the only option I have for "count" (column) is % of grand total. Can someone point me in the right direction to write a measure that will calculate the percentage of the column and be able to graph the result. With regard to the period column, it is coming as P1 and P2 (text format) for each month but I have the periodid column that will be specific to a period (e.g. March20 = 2 Periods: P1=15032020 / P2 = 31032020 ___ April21 2 Periods: P1=15042021 / P2 = 30042021) Please let me know if you need more info. Thank you.Re: Number of Employee - Start of the month
Hi Anonymous & amitchandak My apologies but I was on annual leave. Please find my sample here: https://1drv.ms/u/s!AiS7XVNuQsBCdEmmPcC7MgNIRqQ in the sample, you will see that the first row matches, (303 + 6 -16 =293). However, when we go to Sep 2019-2020 it doesnt (293-13+10 = 296. This is because we have that one employee (ID446) that started on the 1st of September. I am more looking into having the count of employee for the very last day of the month rather than the start. Please let me know if you need more clarifications to help me out. Thanks.Número de empleados - Inicio del mes
Hola comunidad POWER BI, Necesito tu ayuda con una fórmula. Me gustaría saber el número de empleados que tengo al comienzo de un mes determinado. Esta es la fórmula que estoy usando actualmente: SubcontractorMonthStart ? VAR MaxDate ? CALCULATE(MIN('Calendar'[Date]),ALLEXCEPT('Calendar','Calendar'[Month]))//STARTOFMONTH(Calendar'[Date]) VAR EMPCOUNT ( VAR EMPCOUNT) CALCULATE(COUNTROWS(CALCULATETABLE(Drivers, Drivers[StartDate] <- MaxDate, ALL ('Calendar'[Date])), (ISBLANK(Drivers[FinishDate]) Controladores[FinishDate] > MaxDate)) DEVOLVER IF(ISBLANK(EMPCOUNT), 0, EMPCOUNT) La cosa es que la fórmula también se tendrá en cuenta si un empleado es contratado el primer día del mes. En el ejemplo siguiente (resaltado), puede ver que al restar las sales y agregar nuevos entrantes del mes anterior, no se obtiene el número correcto para agosto y esto se debe a un nuevo recluta el 01/09. Es casi como si necesitara adaptar la fórmula para darme el cargo de empleado en el último día del mes anterior? Gracias por tu ayuda.
