Forum Discussion

Leo_Ha's avatar
Leo_Ha
New Member
2 years ago

How to arrange sum

Hi every one ,

 

I have 2 table like this, 

please help me how to get the result in Actual Qty Column,

For example: item A-A-01 have 2 Plan Qty in Table 1, and in Table 2 actual result of Item A-A-01 is 650

how to calculate Actual Qty column in Table 1 

 

 

 

 

Need Help

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

    Please check the below picture and the attached pbix file.

    It is for creating a new column.

     

     

     

    Actual Qty CC =
VAR _qty =
    RELATED ( 'Table 2'[Qty] )
VAR _cumulateplan =
    SUMX (
        FILTER (
            'Table 1',
            'Table 1'[Item] = EARLIER ( 'Table 1'[Item] )
                && 'Table 1'[Plan] <= EARLIER ( 'Table 1'[Plan] )
        ),
        'Table 1'[Plan Qty]
    )
VAR _prevcumulateplan =
    SUMX (
        FILTER (
            'Table 1',
            'Table 1'[Item] = EARLIER ( 'Table 1'[Item] )
                && 'Table 1'[Plan] < EARLIER ( 'Table 1'[Plan] )
        ),
        'Table 1'[Plan Qty]
    )
RETURN
    IF ( _qty >= _cumulateplan, 'Table 1'[Plan Qty], _qty - _prevcumulateplan )