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
v-lionel-msft's avatar
v-lionel-msft
Community Support
6 years ago

manideep547,

 

Could you give a mathematical formula or fake code for [%]? From your description, I still don't understand the logic.

 

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.

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.