Help Adding Calculate Conditions to Switch Function
Need help adding correct calculations into my Switch function using two tables: 1) PPI (premium per phone interaction) table 2) PROD sales table Calculate "Total PPI" using this formula: Total Premium (from PROD Sales)/Total Count of PPI (from PPI) There is active relationship between "Date" and inactive between "Advisor" (not sure if I set it up correctly) I've included my PBI project with sample data and visuals to download/view: Power BI PPI Project Below my existing Switch statement; however I'm not getting correct results: Total PPI = SWITCH( TRUE(), //Case 1: When Advisor and Date selected HASONEFILTER('PROD SALES'[Advisor]) && HASONEFILTER('PROD SALES'[Date]), CALCULATE( DIVIDE([TotalPremium], [PPI Calls])), ), //Case 2: Only date selected HASONEVALUE('PROD SALES'[Date]), CALCULATE( DIVIDE ([TotalPremium],[PPI Calls]), USERELATIONSHIP('PROD SALES'[Date], 'PPI'[Date]) ), //Case 3: Only Advisor is selected HASONEVALUE('PROD SALES'[ADVISOR]), CALCULATE( DIVIDE ([TotalPremium],[PPI Calls]), USERELATIONSHIP('PROD SALES'[Advisor], 'PPI'[ADVISOR]) ), // Default: average over all call times if no or multiple selections DIVIDE([TotalPremium],[PPI Calls]) ) I need to add the following conditions to calculate PPI as well: - Selecting Department Only - Date + Department - Advisor + Date + Department My priority is when selecting a single date to show the correct PPI result. Total Premium selecting the date 5/15: $84,179.52 # PPI Calls from PPI Table for the selected date 5/15 = 142 Total Premium / PPI count = Total PPI Expected Total PPI result: $592.81 TOTAL PREMIUM FOR "Field" Department (from Prod Sales table): $846,573. 08 PPI Count FOR "Field" Department (from PPI table): 1,202 Total Premium / PPI count = Total PPI Expected Total PPI result when "Field" is selected: $704.30 Total Premium for "Call Center" Department (from Prod Sales table): $951,098.02 PPI Count for "Call Center" Department (from PPI table): 1,702 Total Premium/ PPI Count = Total PPI Expected Total PPI result when "Call Center" is selected: $558.81 Expected PPI when no filter is applied: $619.03 Appreciate your help!
Calculated Column To Determine Attendance Status
I am building an attendance dashboard and already integrated holidays and weekends and a calculated column that calculated the durantion between the earliest entry and latest exit timestamps and everything checks out except for the "Attendance Status" calculated column where I am using the SWITCH function with all possible conditions but I am not getting the expected results as can be seen in the screenshot, when the day is "Regular" and the completed hours are equal or more than 8 then it should be fulfilled otherwise it is incomplete same with ramadan but the hours being 5 instead but I "Absent" and "Not Required" are the only results I am getting even when that is not the case. I tried to test out each condition individualy to troubleshoot but to no avail. Below is the dax expression I used: Attendance Status = SWITCH( TRUE(), ISBLANK([Completed_Hours]) && RELATED(Calendar[DayType]) IN {"Weekend", "Holiday"}, "Not Required", ISBLANK([Completed_Hours]) && RELATED(Calendar[DayType]) = "Regular", "Absent", ISBLANK([Completed_Hours]) && RELATED(Calendar[DayType]) = "Ramadan", "Absent", RELATED(Calendar[DayType]) = "Regular" && [Completed_Hours] >= 8, "Fulfilled", RELATED(Calendar[DayType]) = "Regular" && [Completed_Hours] < 8, "Incomplete", RELATED(Calendar[DayType]) = "Ramadan" && [Completed_Hours] >= 5, "Fulfilled", RELATED(Calendar[DayType]) = "Ramadan" && [Completed_Hours] < 5, "Incomplete", RELATED(Calendar[DayType]) IN {"Weekend", "Holiday"} && [Completed_Hours] >= 8, "Fulfilled", RELATED(Calendar[DayType]) IN {"Weekend", "Holiday"} && [Completed_Hours] < 8, "Incomplete", "Unknown" )Query Resources Issue - Actual vs Budget Finance Report
I am trying to construct a matrix that displays my details as rows, presenting five years of data that includes both actual values and budget figures. Specifically, I have five years of actual data and budget values only for the current year, along with calculations for variances between current year (CY) and last year (LY) actuals, as well as the growth from CY actual to LY actual and budget performance. However, I'm facing a challenge because I lack a dedicated column for these particulars; instead, the data is sourced from various MIS columns across three different tables, with inconsistent naming conventions. I can create the visual representation, but it breaks when I attempt to filter the months using a slicer.
How to Capture Changes by Specific Filters
Hello I am fairly new to PowerBI and not sure if this is something I can do but I have a visual that I need to get the Net change by Month and Country. I have provided a photo below with the current visual. The goal is to create a new measure or column that would be able to show me either the sum of the values in the red or show the difference between the Starting Headcount and the Ending Headcount. The issue I am having is those values under USA and IND are values of column Roll on/ Roll Off and I am having trouble trying to distinguish them, I have tried using the Switch function but no success. Could anyone help point me in the right direction? Thank you.
PowerBI Preset date selection and custom selection with comparison period
Hello, We are trying to make a preset date list that compares the same time period last year, whilst also having the option to use a custom filter with the ability to also use a custom comparison date. For some context we have set up the preset date list and have added a dax calcualtion to show the same time period for the previous year i.e. it will show 01/05/2024 compared to 01/05/2023. However, we can not then get a custom date selection with the option to use an additional calendar for a comparison. We've found loads of videos showing how to do this for a single date, but not including a comparison date. Any help would be hugely appreciated! Thanks in Advance! DataContetCalling a semaphore measure in a new measure ; switch/ifcode
Hello all, I have a measure that calculates semaphore value based on Target column and Value column, but it works on single filter selection. Now i want to create a new measure that will call upon semaphore one and do next logic: If ,for two selected KPI (semaphore values), one is green and other is yellow or black ,then display green sempahore otherwise red one.. I've tried muliple things but each time it only works for single selection of filter....probably because of SELECTEDVALUE function. Any help suggestion is welcomed! Semaphore measure: Semaphores NEW 2024 YTD = VAR CurrentMonth = MONTH(TODAY()) VAR JanToCurrentMonthVALUE = CALCULATE( SUM('Actual_2024'[Value]), FILTER( 'Actual_2024', 'Actual_2024'[Month] >= 1 && 'Actual_2024'[Month] <= 12 ),'Actual_2024'[Month] <= CurrentMonth ) var notisblank = CALCULATE(COUNTROWS('Actual_2024'),FILTER(Actual_2024,Actual_2024[Month]>= 1 && Actual_2024[Month]<= 12), NOT(ISBLANK('Actual_2024'[Value]))) var JanToCurrentMonth = DIVIDE(JanToCurrentMonthVALUE,notisblank) // VAR JanToCurrentMonthTArget = CALCULATE( // SUM('Targets_2024'[Target]), // FILTER( // 'Targets_2024', // 'Targets_2024'[Month] >= 1 && 'Targets_2024'[Month] <= 12 // ),'Targets_2024'[Month] <= CurrentMonth-1 // ) // var Target = // DIVIDE(JanToCurrentMonthTArget,notisblank) var TargetX =CALCULATE(CALCULATE(SUM(Targets_2024[Target]),Targets_2024[Month]= 2024)) VAR FourPercentage = TargetX / 20 VAR FourPercentageAbs = ABS(FourPercentage) RETURN IF( SELECTEDVALUE('CpC KPIs_2024'[Semaphore]) = "G" , IF(ISBLANK(JanToCurrentMonth) , "⚫" /*"#808080"*/ , IF(ISBLANK(TargetX) , "⚫" /*"#808080"*/ , IF(JanToCurrentMonth >= TargetX , "🟢" /* "#67BB06" */ , /* green */ IF((JanToCurrentMonth-TargetX)* -1 <= FourPercentage , "🟡" /*"#fffb00"*/ ,/* yellow */ "🔴" /* "#F00202" */ /* red */ )) )), /* RED ABOVE TARGET */ IF( SELECTEDVALUE('CpC KPIs_2024'[Semaphore]) = "R" , IF(ISBLANK(JanToCurrentMonth) , "⚫" /*"#808080"*/ , IF(ISBLANK(TargetX) , "⚫" /*"#808080"*/ , IF(JanToCurrentMonth <= TargetX , "🟢" /* "#67BB06" */ /* green */ , IF((JanToCurrentMonth-TargetX) <= FourPercentage ,"🟡" /* "#fffb00" */ ,/* yellow */ "🔴" /* "#F00202" */ /* red */ )) )),"⚪")) __________________
