Forum Discussion

Eyal's avatar
Eyal
Icon for Helper II rankHelper II
5 years ago
Solved

Calculate tests per date per item

Hi   I am strugeling with the following, I have two tables.  Table A - a claculated table (so cant use power query) - get the number of item produced in each step by date Table B- is a key table...
Need Help
  • Eyal's avatar
    Eyal
    5 years ago

    Dear all 

     

    I was able to find a solution.

    hope it helps someone 

     

    step 1 :

    creat a new table based on summerization  of the Table A mentioned above

    Plan =
    SUMMARIZE(
    FILTER('TableA',NOT('TableA'[Activity] IN { "Not relevent item 1", "N/R", ......})),
    'Calendar'[Year#],
    'Calendar'[Month#],
    'Calendar'[MonthName],
    'TableA'[project],
    'TableA'[Activity],
    'TableA'[BR Scale],
    "Items",sum('TableA'[Run])
    )

     

    Step 2 make a relationship between new table and table B based on the "Step"

     

    for each test a calculated column

    # of test = 

    var test = LOOKUPVALUE(TableB[Test1],TableB[Step],'plan'[Step])
    return
    ('Plan'[Items]*test)

     

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Eyal , if Table A is Table; you have as the source, I think you should better reconsider the source format. If not please share the sample source data for A and B in text format

  • Eyal's avatar
    Eyal
    Icon for Helper II rankHelper II
    5 years ago

    amitchandak 

    I am not sure I follow.

    Table A in my real life scenario is a claculated table.

    It is generated by :

     

    SELECTCOLUMNS(
    GENERATE(
    '1-Task Pool',
    DATESBETWEEN(
    'Calendar'[Date],
    '1-Task Pool'[StartDate],
    '1-Task Pool'[DueDate]
    )
    ),
    "Date",'Calendar'[date],
    "project", '1-Task Pool'[Project],
    "Milestone",'1-Task Pool'[Milestone],
    "Task",'1-Task Pool'[Task],
    "Sub-Task",'1-Task Pool'[Sub-task],
    "FTE",'1-Task Pool'[FTE],
    "ID",'1-Task Pool'[Id],
    "Run",'1-Task Pool'[% Run],
    "Activity",'1-Task Pool'[Activity Type]
    )
     
    The 
    "Run",'1-Task Pool'[% Run], => result in the number of products
    "Activity",'1-Task Pool'[Activity Type] => The step
     
    Table B is exactly as descibed above. 

     Tha data i generated as an example
    Table A    
    Step# of itemsdate  
    step1101/01/2021  
    step1302/01/2021  
    step1505/01/2021  
    step2401/01/2021  
    step2102/01/2021  
    step2103/01/2021  
    step2205/01/2021  
    step3102/01/2021  
    step3104/01/2021  
    step4201/01/2021  
    step4202/01/2021  
    step4304/01/2021  
         
         
    Table B    
    StepTest1Test2Test3Test4
    Step113 1
    Step2   1
    Step3 131
    Step41 1 

     

    Thank you

    Eyal