Forum Discussion

jabueg's avatar
jabueg
Helper I
1 year ago
Solved

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!

"CALCULATE"
"DAX " "DAX help" 'Need help'
"DAX SWITCH"
USERELATIONSHIP DAX Measures
  • Rupak_bi's avatar
    Rupak_bi
    1 year ago

    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.

     

     

     

     

2 Replies

  • Rupak_bi's avatar
    Rupak_bi
    Super User
    1 year ago

    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's avatar
      jabueg
      Helper I
      1 year ago

      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!