Display a different colors for field in chart
Hi, we have to display different colors for current temp in chart below based on user mode selected where iscurrent column is 1. For example, Below visual shows data where deviceid selected is B0965C2B9CF2A6C855A3C245F6B72F1A: The color of the current temp should vary according to the user mode where iscurrent is 1. Therefore in this case, usermode is auto So color should be grey. if it is heat then red & if its cool then blue. Below measure i created, but i am confused how to apply them for above visual when updatedon date column is in x-axis. Dax: Column Color = VAR _UserMode = SELECTEDVALUE('HeatSense_Device'[UserMode]) RETURN SWITCH( TRUE(), _UserMode = "cool", "#12239E", _UserMode = "heat", "#FF8C00", _UserMode = "fan", "green", "#808080" ) Please advise! PFA file hereHeatsense - Copy - Copy.pbix Thanks in advance! SergeiBaklanComplex numbers in Excel
Did you know that Excel can handle complex mathematical operations with complex numbers? Complex numbers, expressed as "a + bi," where 'a' and 'b' are real numbers and 'i' is the imaginary unit, can be powerful tools in various scenarios. Think engineering, physics, signal processing, or even financial modelling. Excel offers native support for complex numbers, and you can perform basic operations like addition, subtraction, multiplication, and division just as easily as with real numbers. If you haven't explored this yet, give it a shot! It's as simple as entering a formula like=IMSUM(A1:B1)or=IMPRODUCT(A1:B1) One nifty feature is the ability to convert complex numbers from rectangular to polar form and vice versa. This can be a game-changer in certain calculations, especially when dealing with phase angles or magnitude-based analyses. For this you can make use of the=IMABS(A1)and=IMARGUMENT()functions. As complex numbers are considered to be text within Excel you will need special functions to perform arithmetic with them, such as IMSUM, IMSUB, IMPRODUCT, and IMDIV. Excel can help you plot complex numbers on the complex plane using the XY Scatter chart! you will need the IMREAL and IMAGINARY functions to extract the real and imaginary coefficients from the complex number to use them to plot the numbers. This feature can be invaluable when dealing with complex data sets or analysing the behaviour of complex functions. If you're interested in learning more about complex numbers, I made a video covering all functions that work with complex numbers in Excel:https://www.youtube.com/watch?v=_A2DIUibkmk Have you worked with complex numbers before in Excel? What specific use case did you have? Were there any obstacles you had to overcome?
Converting excel data into a printable calendar template
My work runs about 200 events per year. These are listed in an excel spreadsheet. However they also have a word template which has the layout of an annual calendar. This means there are months across the top, and dates down the left hand side (like those big annual calendars you see on a noticeboard). Everytime they add an event or edit an event, it has to be done in the excel spreadsheet manually, and then go into the word spreadsheet manually. I am wondering if there is a way to update it in excel and it magically links to a template (or app) that has the look of a wall calendar. It seems there is a lot double handling and opportunity to make mistakes. Thanks TammyDouble Thunking Works Wonders!
Given that most Excel users would not dream of employing one thunk, you might well ask why even consider nested thunks! The use case explored here is to return all the combinations by which one might choose m objects from n(not just a count of options =COMBIN(n,m), but the actual combinations) Knowing that sometimes allows one to deploy an exhaustive search of options to determine the best strategy for a task. Before considering the task further, one might ask 'what is a thunk; isn't it far too complicated to be useful?' All it is, is a LAMBDA function that evaluates a formula when used, the same as any other function. The formula could be an expensive calculation or, rather better, no more than a simple lookup of a term from a previously calculated array. The point is, that whilst 'arrays of arrays' are not currently supported in Excel, an array of functions is fine, after all, an unrun function is little more than a text string. Only when evaluated, does one recover an array. In the example challenge, each cell contains an list/array of binary numbers, which might itself run into the hundreds of terms. A '1' represents a selected object whilst a '0' is an omitted object. Rather like the counts of combinations obtained from Pascal's triangle, each cell is derived from the contents of the cell to the left and the cell above. This is SCAN on steroids, accumulating array results in two directions. Running down the sheet, the new combination contains those of the above cell, but all the objects are shifted left and an empty slot appears to the right. These values are appended to those from the left, in which the member objects are shifted left but the new object is added to the right. So the challenge is to build a 2D array, each member of which is itself an array. The contents of each cell is represented by a thunk; each row is therefore an array of thunks which, for REDUCE to treat it as a single entity, requires it to be securely tucked inside its own LAMBDA, to become a thunk containing thunks. Each pair of rows defined by REDUCE is itself SCANned left to right to evaluate the new row. By comparison the 2D SCAN required for the Levenshtein distance which measure the similarity of text strings was a pushover. I am not expecting a great amount of discussion to stem from this post but, if it encourages just a few to be a little more adventurous in the way they exploit Excel, its job will be done! p.s. The title of this discussion borrows from the Double Diamond advert for beer in the 1960spulling diferent conditions with the IFS fuction
Hello I am trying utilize the IFS function to calculate additional footages pas a certain Unit criteria. based on the length of a short, medium, or long unit and whether it is a normal unit, branch unit or transfer unit as different factors. the maximum length for a long normal unit is 120 ft, for any length branch unit is 25 ft, and for any length transfer is 5 ft. I came up with the formula below but am not getting the right output. =IFS(G4="long"&F4>120,F4-120,I4="branch"&F4>25,F4-25,I4="transfer"&F4>5,F4-5) PHoto of table for referenced:Dax measures total mismatch in visual level
Hi Team, I have below dax measures as follows: Forecast - Target Units_1 = VAR _DailyUnits = [Target consumption per day_1] RETURN SUMX(Points, SUMX(Calendar_, IF(ISBLANK([Actual Units]),_DailyUnits ))) Referenced above measure 'Target consumption per day_1' comes from below: Target consumption per day_1 = VAR minDate = MIN ( 'Calendar_'[Date] ) VAR maxDate = MAX ( 'Calendar_'[Date] ) RETURN CALCULATE ( SUMX ( TargetTimeSeries, VAR _Days = TargetTimeSeries[EndDate] - TargetTimeSeries[StartDate] +1 VAR Result = TargetTimeSeries[Usage] / _Days RETURN Result ), REMOVEFILTERS ( 'Calendar_' ), KEEPFILTERS ( TargetTimeSeries[EndDate] >= minDate ), KEEPFILTERS ( TargetTimeSeries[StartDate] <= maxDate ), TargetTimeSeries[TargetType] = 1 ) The result(Duplicate of Page 3) tab is as below: Expected output total from Forecast - Target Units_1 should add up(4978+4037) to give 9015 in total. But the measure total displays as 18,030(guess so) which is not correct & what we wanted. Another example for above total mismatch is as below: I think its to do with filter context but am struggling to work it out. Please advise! PFA file herePortfolio Performance - v2.13 - Copy.pbix Thanks in advance! SergeiBaklan
How to assign numerical value to a list?
Hello, I am currently working on a new training program for my team and I am running into a little difficulty with formatting. I'd like to get this set so that the selected cell will "read" the cells above it in the column as a numerical value to calculate a percent complete. "Not Started" should be read as a 0 or 0% "In Progress" should be read as a 1 or 50% "Completed" should be read as a 2 or 100% Ideally, then I'd like to have the selected cell tally each cell's value and give me a red, yellow, or green read-out based on how much they have completed. So for example: Column C could be read as 25% Completed, but overall, less than 50% so still RED. Any help or recommendations for this is appreciated. -Cathy
Adding a range of cells based on a criteria and date range
Hi, Can anyone please guide me adding cell range from AA2 to AA100 when cells I2 to I100 having value "ROBOT" and cell AD2 to AD100 contain date range from 01/07/2022 to 31/07/2022? The date format is DD/MM/YYYY. I used the following formula but it is not working: =SUMIFS(AA2:AA100, I2:I100, "ROBOT", AD2:AD100, ">=01/07/2022", AD2:AD100, "<=31/07/2022")
