Transferred

Question

Hi all,I have Date slicerIf the person is in destination 11 before the minimum date of the slicerThat means let consider Id 11 is present in destination 1 in before the minimum date of the slicer same person present in destination 2 but not in destination 1 at that particular period of time in slicer (mindate&amp;&amp;MAXDATE)Here my calculation is divided(1(number of customers are transferred to another destination), the total number of customers in that destination,0)IN retail I have Destination1In F&amp;B I have Destination2DataIDs DATE Destination sector11 1/1/2018 destination1 Retail22 1/12/2018 destination1 Retail11 1/1/2019 destination2 F&amp;B22 1/12/2019 destination1 Retail11 1/1/2020 destination1 Retailif I selected minimum date 1/1/2019 to max 1/12/2019 for this particular period of time customers is transferred from destination1 to destination2(ID 11)so (1/2)*100=50%For destination1 50% of the customers are transferred to destination2OUTPUTDestination&nbsp; &nbsp;Destination&nbsp; &nbsp; %destination1 destination2&nbsp; &nbsp;50%destination2 destination1&nbsp; &nbsp;0%&nbsp;

anonymous · Answer

Hey&nbsp;manideep547&nbsp;&nbsp;Check out this thread as I believe it helps answer your question:&nbsp;https://community.powerbi.com/t5/Desktop/SELECTEDVALUE-with-Slicer-Selection/td-p/381815&nbsp;You may need to create a helper column or table to concat the possible destintation combinations.&nbsp;If this helps please kudo.If this solves your problem please accept it as a solution.

manideep547 · Answer

Anonymous&nbsp; I need only measure&nbsp;

v-lionel-msft · Answer

manideep547,&nbsp;Could you give a mathematical formula or fake code for [%]? From your description, I still don't understand the logic.&nbsp;Best regards,Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

manideep547 · Answer

v-lionel-msft&nbsp;For Example&nbsp;ID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DATE&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DESTINATION1111&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;04/01/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DESTINATION12222&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;05/02/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DESTINATION13333&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;07/04/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DESTINATION21111&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;08/05/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DESTINATION23333&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 06/05/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;DESTINATION1If I select the dates from 01/01/2019 to 01/12/2019&nbsp; then result should be like below&nbsp; like matrix table&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;DESTINATION1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DESTINATION2DESTINATION1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.00%( For destination1 to destination1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 50.00%(&nbsp; &nbsp;For destination1 to destination2&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; how many customers are converted)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; how many customers are converted)DESTINATION2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50.00%( For destination2 to destination1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.00%( For destination 2 to destination 2&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;how many customers are converted)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; how many customers are converted)FORMULA = &nbsp; &nbsp;Number of customers transfer to another destination/Total number of customers in that destination per that particular period of time in slicer&nbsp;&nbsp;For destination1 to destination1&nbsp; how many customers are converted&nbsp; &nbsp;=(0/2)*100=0.0%&nbsp;For destination1 to destination2&nbsp; how many customers are converted&nbsp; &nbsp;=(1/2)*100=50.0%&nbsp;For destination2 to destination1&nbsp; how many customers are converted&nbsp; &nbsp; = (1/2)*100=50.0%&nbsp;For destination2 to destination2&nbsp; how many customers are converted&nbsp; &nbsp; =&nbsp; &nbsp;(0/2)*100=0.0%Thanks for your reply&nbsp;v-lionel-msft&nbsp; please see the above logic&nbsp;&nbsp;

v-lionel-msft · Answer

Hi&nbsp;manideep547 ,&nbsp;Maybe you can refer these formula:&nbsp;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] &amp; "-" &amp; [DESTINATION] 
)

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

Column 6 = 
VAR x = 
CALCULATE(
    MAX([DESTINATION]),
    ALL(Sheet4)
)
RETURN
IF(
    [Column] &lt;&gt; 0,
    IF(
        [DESTINATION] = x,
        x &amp; "-" &amp; MIN([DESTINATION]),
        [DESTINATION] &amp; "-" &amp; x
    )
)&nbsp;&nbsp;Best regards,Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Transferred

6 Replies

Related content

Problem with transfer data for fabric to mysql

Assign (Transfer) Ownership

TLS for data transfer

Shortcut to AWS S3 - AWS Data Transfer Costs

Transfering of lakehouse form one workspace to another