Issues conditional formatting coloring with a sum of measures
Hi community, I have been working on this visual for some time now. I’ve had members help me along the way which has saved me greatly. I am having issues with implementing conditional formatting on a bar chart depending on measures - I have been trying to create a type of bar chart where each bar may be coloured depending on measures. This is how the bar chart looks like: I have used this for conditional formatting: Conditional Formatting Measure = Switch(TRUE(), SELECTEDVALUE('Aggregate'[PerformanceLevel]) = "Meets " && [Meets%]+[Exceeds%] > 90, "orange", SELECTEDVALUE('Aggregate'[PerformanceLevel]) = "Marginal" && [Marginal%] + [Below%] > 40, "yellow", SELECTEDVALUE('Aggregate'[PerformanceLevel]) = "Exceeds" && [Exceeds%] > 30, "green", SELECTEDVALUE('Aggregate'[PerformanceLevel]) = "Below" && [Below%] > 15, "red", "grey") When there is a sum used in the condition, the bar does not change colour – the sum is not taken into account. I have many instances where Marginal+Below>40 and Meets+Exceeds>90 but the bar doesn't change colour. Here is a link to a sample workbook: google drive I would really appreciate any help and guidance on this. I don't understand why the sum is being ignored.Column or measure Conditionals
Hi, I have a column with this conditions: ConditionalColumn= VAR userID= [UserID] VAR IDduplicated = CALCULATE(COUNTROWS(table), ALLEXCEPT(table, table[UserID])) > 1 VAR shopIDduplicated = IF( IDduplicated, CALCULATE( COUNTROWS(table), ALLEXCEPT(table, table[UserID]), table[shopID] = EARLIER(table[shopID]) ) > 1, FALSE() ) RETURN IF( IDduplicated && shopIDduplicated, "Yes", "No" ) I would rather, if UserID is duplicated, for each duplicated UserID, take their shopID. If the shopID is duplicated, put "Yes"; otherwise, put "No". Below, I show you what this conditional column is doing and what i want: Example: I have 3 duplicated UserIDs, but 2 of them have the same shopID. I want the conditional column to show "Yes", only for those 2 lines; for the oder UserID, it becames "No". What's wrong is in lines where UserID is "22" (lines number 2-3-4). It has "Yes" for all 3, but first shopID isn't equal to others, so it should be "No", "Yes", "Yes". Conditional//shopID//categoryID//userID//YearStart;MonthStart;DayStart//YearFinish;MonthFinish;DayFinish Column No 111 1 11 2023 November 10 2023 October 9 Yes 222 2 22 2023 February 28 2023 January 16 Yes 333 3 22 2023 October 6 2023 October 1 Yes 333 3 22 2023 October 16 2023 October 16 No 444 4 33 2023 June 1 2023 May 23 Yes 555 5 44 2023 November 3 Yes 555 5 44 2023 January 19 2023 January 10 Yes 555 6 44 2023 July 28 2023 February 13 Yes 777 5 44 2023 November 2 2023 August 14 Yes 666 9 55 2023 May 31 2023 April 17 Yes 666 9 55 2023 July 21 2023 June 1 PS: The next level would be: once you have those "Yes" values, filter them for the same UserID, shopID, and check if they have the same categoryID. If they do, this would be the real "Yes"; the others would be "No". In that case, taking the table example, for the UserId "44", the correct sequence would be: Yes-Yes-No-No. PS2: The final level would involve, once you have level 2, comparing the start date with the end date for lines that meet the previous criteria. If datediff is <=5 days, it should be "Yes"; otherwise, "No". Next step would be to do the same for the end date, comparing it with other lines' start dates. Before this, thake the next line and do the same... However, it's more important to have levels 1 first and 2 correct, not this level. Any help is appreciated. Thanks!
Continuous subtracting of values from previous row in a different column
Hi all, I am working on a Power BI dashboard that is forecasting values up until 2030, depending on projects that are under implementation between 2022 to 2030. I have one table [Actual] that contains actual values from 2021 and 2022 which I have at a monthly frequency. I also have another table [Project Impacts] that contains the projects, with the impacts that they have on the actual values, and this is available from 2022 to 2030, at a quarterly frequency. There are a few important measures relevant to this problem. From the [Actual Values] table, we have ‘Actual Values’. From the [Project Impacts] table, we have a measure named ‘Estimated saving from projects’, which calculates how much savings there are based on the projects each quarter. The table below shows the measures next to each other. What I want to do is subtract the ‘Estimated savings from projects’ from the ‘Actual Values’, so that I am left with the ‘Forecast Values’. This is the results I get after doing this (image below). As you can see from the image above, the ‘Forecast Values’ seems to be working properly, up until 2023/Q1. This is because there is no current data for 'Actual Values' after 2022, so the ‘Estimated saving from Projects’ is just subtracting from 0, hence the negative values in the far right column. What I want to do is keep the calculation the same up until the end of 2022, but after 2022, the ‘Estimated saving from Projects’ should be subtracted from the last quarters value. So as an example, the 2023/Q1 ‘Estimated saving from Projects’ value of 7.87 should be subtracted from the 53.51 value from the 2022/Q4 value in the ‘Forecast Value’ measure, giving a value of 45.64 instead of the value -7.57. Then the same process should repeat, so the 7.87 from 2023/Q2 should be subtracted from the 45.64 from the previous quarter and so on. Does anyone know of a DAX formula, calculated column, or any solution to the problem above? Many thanksAdding in a Condition that deals with quotes that feature a recall.
Hi everyone, I'm in need of some help... I'm trying to edit my DAX Measure seen below to add in a condition that would deal with quotes that feature a recall in its history. Currently, the Measure takes the submission date from the final line of the history, and subtracts that by the response date. Essentially, the condition would be able to detect if a quote has a recall in its history and would take the response date from the recalled line and take the place of the submission date in the final line subtraction. I have a screenshot attached below to hopefully provide a good visual example of what I would be looking to do. Below is the DAX Measure that I'd be looking to put the condition into: Quote Turnaround Time for Approval Report Test File = VAR ResponsesThisQuoteVersion = CALCULATETABLE ( DISTINCT ( 'Test Approval Report'[Response Date] ), ALLEXCEPT ( 'Test Approval Report', 'Test Approval Report'[Quote Number], 'Test Approval Report'[Quote Version] ) ) var ThisResponse = AVERAGEX('Test Approval Report','Test Approval Report'[Response Date]) var SubDT = AVERAGEX('Test Approval Report','Test Approval Report'[Submission Date ]) VAR MaxResponse = MAXX( ResponsesThisQuoteVersion, 'Test Approval Report'[Response Date] ) RETURN IF ( ThisResponse <> MaxResponse, BLANK(), MaxResponse - SubDT ) *24 * 60 For what it's worth, I was also able to create calculated columns that would retrieve the previous row's value. Not sure if that's a relevant detail to provide or not. For whomever takes the time to read this and is able to provide suggestions/help, thank you!
HOW TO SUM A VARIABLE THAT COMES FROM A MEASURE
Hello guys! I have to resume values from 3 different lines of production, which are "Lee Pack", "Rad Pack" and "Manual". To calculate the variable "Velocidad teorica" I use this conditional: Velocidad teorica = IF(OR([promedio]=1, [promedio]=2), (SUM(Estandares[Rendimiento maquina])/450), IF([promedio]=3,(SUM(Estandares[Rendimiento manual])/450), "0")) where 1 means "Lee Pack", 2 means "Rad Pack" and 3 means "Manual" Due to it is a conditional, in the table's total the value shown is the condition if false from the second conditional. Other variable is "Tiempo Teórico" where tiempo teorico = sum(Hora[No. Unidades])/[Velocidad teorica] so if "Velocidad teórica" is 0, then it would show infinite. Lastly, the variable "% CUMPLIMIENTO" is calculated: % CUMPLIMIENTO = if(([tiempo teorico]/[tiempo real])>100, "0", ([tiempo teorico]/[tiempo real])) My problem is that I can´t visualize the sum of "tiempo teórico", and i Think its because the conditional, same as "velocidad teórica" and "% cumplimiento". Do you know a way to do it? The data table is available in the link https://docs.google.com/spreadsheets/d/1QcCAdk9oMWi__oWiYaWkTop5OXn5feCg/edit?usp=sharing&ouid=103939786031930093563&rtpof=true&sd=true https://drive.google.com/drive/folders/1BpRDGXwfnV7QKtacQkmnR9kM7pIuxH06?usp=sharingConditional formatting on the basis of more than 1 column
I need to apply Custom Font color formatting on the bases of these 2 columns. If the value in Col 1 is between -10 and +10 If value in Col 2 is between -10 and +10 then Green Else Red I tried following Switch condition but it is not working #418344 is green color and #DF595C is red. SWITCH( TRUE(), Col 1 <=10, "#418344", Col 1 >=-10, "#418344", Col 2 <=10, "#418344", COl 2 >=-10, "#418344" ,"#DF595C" )Trying to make a formula conditional by date depending on time from current date
Hi all, I am trying to implement the Excel formula in the below screenshots into DAX for use in a Power BI dashboard. Here is my data, the formula, and an explanation of what the formula does: If a date is over 6 days ago, I do not want to adjust its value. If a date is within 6 days of the current date but more than 2 days ago (so 3, 4, 5, or 6 days ago), I want to increase it by 11.5% each day for the number of days remaining until it hits 6 days. If a date was within the past 2 days, I want to increase it by 13% each day for the number of days remaining until it hits 6 days. The formula shown below accomplishes that in Excel. I am now trying to port the formula into Power BI. I used a formula identical to the one above, but I replaced DAYS() with DATEDIFF(). However, I get an error when trying to reference the date column, saying that "A single value for column 'Date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
