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!
Variance between Two Years on same Date Axis using same Amount Column
Unfiltered: Filtered: The task at hand is simple, take the 2025 values (Food Revenue, Beverage Revenue, etc.) and subtract the 2024 values from the correlated field (first screenshot). However, I am running into issues with the date axis it lies on and how to correctly display only the months needed. The formula used takes the sum of the current year (CY, Dec 2024 - Mar 2025) for a specific account, and subtracts it from the balance of the prior year (LY, Dec 2023 - Mar 2024). This does in fact work, as the values shown in the second screenshot are correct for Food Revenue Variance for the months needed thus far (fiscal year starts in December, so December 2024 - March 2025). However, when I filter to just show the months needed, the values revert to only show the balances correlated with the current year (CY). Any ideas on how to show this variance correctly? In layman's terms, basically need to INCLUDE data from all months relevant (Dec 2023 - Mar 2024, Dec 2024 - Mar 2025), but I only need to DISPLAY the months with the correct values for variance (Dec 2024 - Mar 2025). DAX measure: VARI Food Revenue = VAR CY = CALCULATE(SUM(ConsolidatedReport[Amount]), ConsolidatedReport[GLAccount] = "450000", DateDim[CurrentFiscalYear] = 0) VAR LY = CALCULATE(SUM(ConsolidatedReport[Amount]), ConsolidatedReport[GLAccount] = "450000", DateDim[CurrentFiscalYear] = -1, REMOVEFILTERS(DateDim[Date].[Year])) RETURN CY - LY
Calculation Headeach
I Have this type of data And I want to run a repport like this The first repport work fine, but when i collapse the society, the cumulative to do is not the cumulative I want (The cumulative for all the society regardless the month). If you have ideas please don't hesitate to give me your feedback. Kind regards
