Forum Discussion

manideep547's avatar
manideep547
Helper III
6 years ago

Transferred

Hi all, I have Date slicer If the person is in destination 11 before the minimum date of the slicer That means let consider Id 11 is present in destination 1 in before the minimum date of the slic...
Need Help
manideep547's avatar
manideep547
Helper III
6 years ago

v-lionel-msft 
For Example 
ID                      DATE                    DESTINATION
1111                 04/01/2019           DESTINATION1
2222                 05/02/2019           DESTINATION1
3333                 07/04/2019           DESTINATION2
1111                 08/05/2019           DESTINATION2
3333                  06/05/2019          DESTINATION1

If I select the dates from 01/01/2019 to 01/12/2019  then result should be like below  like matrix table 
                                    DESTINATION1                                                     DESTINATION2
DESTINATION1          0.00%( For destination1 to destination1                          50.00%(   For destination1 to destination2 

                                    how many customers are converted)                    how many customers are converted)

DESTINATION2           50.00%( For destination2 to destination1                          0.00%( For destination 2 to destination 2 
                                     how many customers are converted)                    how many customers are converted)

FORMULA =    Number of customers transfer to another destination/Total number of customers in that destination per that particular period of time in slicer 

 For destination1 to destination1  how many customers are converted   =(0/2)*100=0.0%
 For destination1 to destination2  how many customers are converted   =(1/2)*100=50.0%
 For destination2 to destination1  how many customers are converted    = (1/2)*100=50.0%
 For destination2 to destination2  how many customers are converted    =   (0/2)*100=0.0%

Thanks for your reply v-lionel-msft  please see the above logic  




v-lionel-msft's avatar
v-lionel-msft
Community Support
6 years ago

Hi manideep547 ,

 

Maybe you can refer these formula:

 

Column 2 = 
VAR x = 
CALCULATE(
    DISTINCTCOUNT([ID]),
    ALLEXCEPT(
        Sheet4,
        Sheet4[DESTINATION]
    )
)
VAR y = 
CALCULATE(
    COUNT([ID]),
    ALLEXCEPT(
        Sheet4,
        Sheet4[DESTINATION]
    )
)
RETURN
y - x

Column 5 = 
IF(
    [Column 2] = 0,
    [DESTINATION] & "-" & [DESTINATION] 
)

Column = 
VAR x = 
CALCULATE(
    COUNT(Sheet4[DESTINATION]),
    ALLEXCEPT(
        Sheet4,
        Sheet4[ID]
    )
)
RETURN
IF(
    x >1,
    x/2
)

Column 6 = 
VAR x = 
CALCULATE(
    MAX([DESTINATION]),
    ALL(Sheet4)
)
RETURN
IF(
    [Column] <> 0,
    IF(
        [DESTINATION] = x,
        x & "-" & MIN([DESTINATION]),
        [DESTINATION] & "-" & x
    )
)

 

 

Best regards,
Lionel Chen

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