Help Adding Calculate Conditions to Switch Function

[jabueg]

 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!

[Rupak_bi · Accepted solution]

Hi @jabueg ,

The sample file you shared, have only 102 rows in PPI table and 48 rows on 15/5. as per these data, the calculation working fine. please share a revised sample file and the expected result based on the sample file to wokr on this further and validate.

[jabueg]

Sorry, here is the revised sample file! There should be 2,904 rows in the PPI table.

PBI PPI Project 

I need to see these results when each "Department" is selected from the middle top visual:

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

Also, need to add conditions to the Switch to calculate PPI when selecting from slicer:

- Date + Department
- Advisor + Date + Department

Example 1: Date + Department 

If selecting date 5/16 and "Call Center" department,  Total premium = $28,843.80; Count of PPI is 52 from PPI Table.

Result = $554.68

Example 2: Advisor + Date + Department

When selecting JOHN SMITH and date 5/12/25 and "Field" Department = Total Premium = $16,464.60; Count of PPI = 21 from PPI Table. 
Result = $784.02

Thank you in advance!