Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Find the attachment rate

Hello everyone! I am new here and hope to give back to the community when my skills are as good as yours. 

 

The data set is Direct Query PowerBI so I am not able to transform the data.

 

I am trying to the find the attachment rate for our travel agents. The attachment rate is Travel Trips sold with Insurance / Total Trips sold.

 

Each product has it own row but a "Trip" is determined by its trip number.

 

 

 

This is the result I have been trying to achieve. 

 

 

 

TOTAL TRIPS BOOKED = The amount of trips booked by the teller(travel agent)

INS Only = When the trip involves insurance only. It could be 1 or more products of insurance

TRIPS (HOTEL,CAR, FLIGHT) with INS = When the trip invloves a hotel, car, flight along with insurance

TRIPS WITHOUT INS = When the trip invloves a hotel, car, flight without insurance

Attachment Rate (TRIPS WITH INS/TOTAL TRIPS BOOKED)

 

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    According to your description, here's my solution.

    1.Create a new table, and make relationship between the two tables.

    Table 2 =
UNION (
    ROW ( "TELLER", "ASHBY" ),
    ROW ( "TELLER", "SALLY" ),
    ROW ( "TELLER", "KEVIN" )
)

    2.Create five measures.

    TOTAL TRIPS BOOKED = DISTINCTCOUNT('Table'[TRIP])
    INS ONLY =
COUNTX (
    DISTINCT (
        FILTER (
            ALL ( 'Table' ),
            'Table'[PRODUCT] = "INS"
                && CALCULATE (
                    DISTINCTCOUNT ( 'Table'[PRODUCT] ),
                    FILTER ( 'Table', 'Table'[TRIP] = EARLIER ( 'Table'[TRIP] ) )
                ) = 1
        )
    ),
    'Table'[TRIP]
)

    TRIPS with INS =
COUNTX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[PRODUCT] = "INS"
            && CALCULATE (
                DISTINCTCOUNT ( 'Table'[PRODUCT] ),
                FILTER ( 'Table', 'Table'[TRIP] = EARLIER ( 'Table'[TRIP] ) )
            ) > 1
    ),
    'Table'[TRIP]
) + 0

    TRIPS without INS = [TOTAL TRIPS BOOKED]-[INS ONLY]-[TRIPS with INS]
    Attachment Rate = DIVIDE([TRIPS with INS],[TOTAL TRIPS BOOKED])

     Get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Syk's avatar
    Syk
    Icon for Resident Rockstar rankResident Rockstar
    4 years ago

    Create a new measure and try this! 

    Attachment Rate = DIVIDE(CALCULATE(DISTINCTCOUNT('Table'[Trip #]),'Table'[Product]="INS"),DISTINCTCOUNT('Table'[Trip #]),0)

     

    I did not enter all of your data in the screenshot but got this as a result which seems correct for the data I DID enter.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello Syk ,

     

    Thank you for responding. Is there a way that I can have measure for each of the columns and how you would calculate them?

     

    Thank you

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Share your data in an MS Excel file.  If possible, in another tab of the Excel file, show the formulas that you have used in MS Excel to generate the result that you have shown in Table2 of your original post.  I will try my best to translate those Excel formulas into the DAX language.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

    According to your description, here's my solution.

    1.Create a new table, and make relationship between the two tables.

    Table 2 =
UNION (
    ROW ( "TELLER", "ASHBY" ),
    ROW ( "TELLER", "SALLY" ),
    ROW ( "TELLER", "KEVIN" )
)

    2.Create five measures.

    TOTAL TRIPS BOOKED = DISTINCTCOUNT('Table'[TRIP])
    INS ONLY =
COUNTX (
    DISTINCT (
        FILTER (
            ALL ( 'Table' ),
            'Table'[PRODUCT] = "INS"
                && CALCULATE (
                    DISTINCTCOUNT ( 'Table'[PRODUCT] ),
                    FILTER ( 'Table', 'Table'[TRIP] = EARLIER ( 'Table'[TRIP] ) )
                ) = 1
        )
    ),
    'Table'[TRIP]
)

    TRIPS with INS =
COUNTX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[PRODUCT] = "INS"
            && CALCULATE (
                DISTINCTCOUNT ( 'Table'[PRODUCT] ),
                FILTER ( 'Table', 'Table'[TRIP] = EARLIER ( 'Table'[TRIP] ) )
            ) > 1
    ),
    'Table'[TRIP]
) + 0

    TRIPS without INS = [TOTAL TRIPS BOOKED]-[INS ONLY]-[TRIPS with INS]
    Attachment Rate = DIVIDE([TRIPS with INS],[TOTAL TRIPS BOOKED])

     Get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.