Forum Discussion

saad_mce's avatar
saad_mce
Icon for Helper I rankHelper I
4 years ago
Solved

Does calculate work with virtual table/columns

Hi all, I am trying to measure how many products in a factory achieved the production plan in a certain month. Monthly production plan table is divided into daily plans in a different table. For the...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi saad_mce 
    To be honest I did not fully read your requirement. Only the part the states the problem with the grand total. I did not understand the need for the virtual table in the "Plan Achievement2" measure Why don't you just use the calculation straight away? 

    Plan Achievement2 :=
SUM ( 'Actual_Production'[Actual Production] )
    / SUM ( 'ZLTP_Prod_Jan22_Daily'[Qty/Day] )

    For the other measure measure just use

    SKUs Achieved 1 New:=SUMX ( VALUES ( New_MSAUDIT[Material] ), [SKUs Achieved 1] )
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi saad_mce 
To be honest I did not fully read your requirement. Only the part the states the problem with the grand total. I did not understand the need for the virtual table in the "Plan Achievement2" measure Why don't you just use the calculation straight away? 

Plan Achievement2 :=
SUM ( 'Actual_Production'[Actual Production] )
    / SUM ( 'ZLTP_Prod_Jan22_Daily'[Qty/Day] )

For the other measure measure just use

SKUs Achieved 1 New:=SUMX ( VALUES ( New_MSAUDIT[Material] ), [SKUs Achieved 1] )
  • saad_mce's avatar
    saad_mce
    Icon for Helper I rankHelper I
    4 years ago

    Hi tamerj1,

    Thanks for your quick response.

    As I was using virtual table for "SKUs achieved 1" measure, I thought I would try this for "Plan Achievement" as well. This is to get my head around the virtual table concept. I am happy to use the formula as you suggested.

    Your suggestion is great for the grand total. Your recommended formula works perfect.

    I was thinking if I could avoid the two measures to get the numbers right for SKU level and grand total.

    I was wondering if I create a variable for "SKUs Achieved 1" inside the "SKUs Achieved 1 New". For sume reason, this approach is not working. The sum is still not correct.

     

    Anyway, if this can not be fixed, I will just use the two step measure.

    Thanks again for your suggestions and have a great weekend.

     

    SKUs Achieved 1 new:=VAR SKUsAchieved1=(SUMX(ADDCOLUMNS(SUMMARIZE('ZLTP Prod Jan 22 Daily','ZLTP Prod Jan 22 Daily'[Resource],'ZLTP Prod Jan 22 Daily'[Material],'ZLTP Prod Jan 22 Daily'[Family Desc]),"Plan Achievement2",SUM('Actual Production'[Actual Production])/SUM('ZLTP Prod Jan 22 Daily'[Qty/Day]),"SKUs achieved",calculate(IF((SUM('Actual Production'[Actual Production])/SUM('ZLTP Prod Jan 22 Daily'[Qty/Day]))>0.8,1,0))),[SKUs achieved]))

    Return
    SUMX ( VALUES ( 'New MSAUDIT'[Material]), SKUsAchieved1)