How to Get Value Percentage based on Criteria in Table?
I have an a Powerbi Table showing a criteria if a phase has been started on time or delayed. This is based on an additional column that shows "Not started" "On time" or "Delayed" based on the planned and actual dates. Help needed: Can someone help guide me on how I can get the percentage of the values in each criteria (depending on whether they not started, on time, or delayed)? For example, the result I want to see is: Out of the 10 projects, 78% of the project phases is not started 22% are on time 0% are delayed Appreciate your inputs 🙂
Conditional Formatting/Coloring Values
Hello all! I have created a matrix and a table on the same tab of a Power BI Report. (See 1st picture below) The table on the left uses conditional columns which I manually inserted into the table. (For example: If the time is 8:00 AM, then 15 staff is then 49, and so on and so fourth). Both visuals are useing the same times. The values in the matrix are using a column called StudentID, and the values are as Count (Distinct). I was asked to find a way to color the values on the right as follows: If the Value is above the 5 Staff number, then color it Green If the Value is below the 15 Staff number, then color it Red Ex: On January 25, 2022 the value is 30. This value would be Red If Value is in between, then color is Yellow Here is an example of the data I am using Time Date StudentName StudentID College CreditHours 15 Staff 5 Staff 8:00 AM January 27, 2022 Joseph Mann 005866 Business 14 49 51 9:00 AM January 29, 2022 John Doe 447856 Business 16 77 79 9:30 AM January 25, 2022 Jane Doe 555013 Law 18 70 72 10:00 AM January 25, 2022 Ryan Pierce 554876 Graduate 16 61 63 8:00 AM January 26, 2022 Jannette Richardson 332146 Education 15 49 51 12:00 PM January 27, 2022 Dameon Travis 958741 Business 15 45 47 2:00 PM January 28, 2022 Marney Phillips 004584 Education 15 63 65 I am not really 100% sure how this would be able to get formatted the way I am being asked. Would this be using Variables in a calculation? I struggle so much with variables and some DAX Functions. I've tried to do drop down on the values and conditionally format, however that really didn't work for my case. I did attempt to do conditional formatting. However with the rules, I am not able to do conditional formatting based on both 15 Staff and 5 Staff. So it is being sort of odd with it because I am trying to compare the Value between both 15 and 5 Staff. Is there some sort of DAX Expression that can maybe be written to compare the value between both 15 and 5 Staff?
Get total miles driven in a table
Hi Everyone, I have a table in power bi which is rows for each driving trip users enter. They only enter the ending mileage of the car. What I need to do is show in the table how many total miles driven based on taking the ending trip mileage and subtracting it from the last ending trip entered for that car. Here is the table so far for 1 vehicle. As you can see you can have multiple trips entered on the same day. So if I look at the second one in the list the miles driven should be 22 because 114319 the ending mileage - the ending mileage of the trip before it is 22 miles driven. How would I do this?DAX Calculate with Filter
Hi there, I have a sales data table as below. Each customer has more than 1 contract. I want to calculate the total sales between the latest contract date for each customer and up to 31 Dec 2021. The result i am expecting is the yellow cells below and the total should be $1490 (1300+100+90). Dax: 1. Latest Contract Start Date = max(Sales[Contract Date]) 2. Total Sales from Latest Contract Start to end of Dec 2021 = CALCULATE([Total Sales],DATESBETWEEN('Calendar'[Date],[Latest Contract Start Date], date (2021,12,31))) Below is the PowerBI result. at customer level, it is showing correct calculation. However, at total level, the $90 total sales is wrong as it is picking up the latest contract start date from the full list. How should i change the formula so that at total level, it will show as $1490? Thank you!!Past Week Count of Customers
Hi All, I am trying to calculate the variance between the count of customers on a weekly basis and no I am not able to use the DATE functions as Power BI does not calculate for "Weeks". I am not able to use -7 days as well, as not all month's start/end on the same day. Firstly, I would need to get the count of customers from the past week. I managed to get the past week net sales to show accurately, and tried to apply the same formula for the count of customers. However, I am not getting the right numbers. Please refer to the attached image in which, the figures for PW No. of Customers should reflect the figure from the week before. However, from the image you can see that for Row 3, 95 is shown instead of 78, and for Row 4, 99 is shown instead of 81... and so on and so forth. I would greatly appreciate any form of help. Formula for Count of Customers: -- Just a distinct count of the customer name field CY No. of Customers = DISTINCTCOUNT(FACT_Sales[CH2 Customer/Vendor Name]) Formula for Actual Customers: -- Because I have MTD/QTD/YTD slicers Actual_Customers = SWITCH('Selected Period'[Selected Period], 1, [CY No. of Customers], 2, CALCULATE([CY No. of Customers],DATESQTD(DIM_Calendar[Date])), 3, CALCULATE([CY No. of Customers],DATESYTD(DIM_Calendar[Date]))) Formula for Past Week count of Customers: PW No. of Customers = VAR CurrentWeek = SELECTEDVALUE(DIM_Calendar[Week of Year]) VAR CurrentYear = SELECTEDVALUE(DIM_Calendar[Year]) VAR CurrentMonth = SELECTEDVALUE(DIM_Calendar[Month]) VAR MaxWeekNumber = CALCULATE(MAX(DIM_Calendar[Week of Year]), FILTER(ALL(DIM_Calendar), DIM_Calendar[Year] = CurrentYear-1)) RETURN SUMX( FILTER(ALL(DIM_Calendar), IF(CurrentWeek = 1, -- If week in current context is 1, find the 53rd week of the prior year DIM_Calendar[Week of Year] = MaxWeekNumber && DIM_Calendar[Year] = CurrentYear - 1, DIM_Calendar[Week of Year] = CurrentWeek -1 && DIM_Calendar[Year] = CurrentYear && DIM_Calendar[Month] = CurrentMonth)), -- else 'Measures Table'[Actual_Customers] )Calculation Involving Duplicates
Without deleting duplicates, how do I write a formula to calculate the total scrap percentage by date? Scrap% = (Machine Scrap/(Machine Scrap + Good Quantity)) * 100 Production Day Machine Scrap Good Quantity 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 33103 734164 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589 06/01/2023 12:00:00 AM 29452 744589
Creating matrix from two tables and a calculation
Hello, I'm trying to do a matrix with 2 tables, but it doesnt do the calculation properly. This are my 2 tables: Where JIRA.Intervention Area = LocationsCoordinates.Location On the matrix, the total cost is incorrect (single values and totals): (JIRA.Resolved; LocationsCoordinates.Location; LocationsCoordinates.TotalCost) Here we can see that, for example, on 14-04-2023 there is 7 rows in "Portugal - Lisboa": The correct calculation for this ex should be 7 * 175,06€. Also the totals are incorrect too. Thank you for your help.Percentage of a group
Hi, I feel like this might be a little more straight forward than I think and I'm overthinking it. I have a data set that looks something like this: Program application # met standard binary A 1 1 B 2 0 B 3 1 B 4 0 C 5 1 C 6 1 C 7 0 D 8 1 What I am looking for is the percentage of programs that are meeting the standard. So if I were to calculate for the above, 75% of programs are meeting the standard, programs A, C and D are meeting the standard. I believe it's a calculation but I can't figure it out Any help that can be provided would be appreciated. Thanks in advance, Vanlang
