locked
Sum with conditional in Powerpivot RRS feed

  • Question

  • Hi everybody,

    I have 2 Table below:

    Table 1:

    Product No Year Month Receiving Quantity
    A 2015 6
    B 2015 6
    C 2015 6


    Table 2:

    Product No Receiving Quantity Year Month
    A 100 2015 6
    B 200 2015 6
    C 100 2015 6
    A 250 2015 6
    B 300 2015 5
    C 400 2015 5
    A 250 2015 5
    B 300 2015 5
    C 400 2015 5


    All I want is In column Receiving Quantiy in Table 1, it will show the Total Receiving Quantity in Table 2 which match Product No, Year and Month.

    The result should be like this:

    Product No Year Month Receiving Quantity
    A 2015 6 350
    B 2015 6 200
    C 2015 6 100

    I try a lot of formular but it does not work. Please help me in this case.

    Thank you very much.



    Monday, June 29, 2015 7:39 AM

Answers

  • Hi SaigonCitizen,

    I suppose you don't have a relationship between Table 1 and Table 2. In this case, you can create a calculated column with the formula

    =CALCULATE(SUM(Table2[Receiving Quantity]);Table2[Year]=EARLIER([Year]);Table2[Month]=EARLIER([Month]);Table2[Product No]=EARLIER([Product No]))

    • Marked as answer by SaigonCitizen Wednesday, July 1, 2015 9:25 AM
    Monday, June 29, 2015 1:53 PM
    Answerer
  • Hi Saigon,

    According to your description, you need to get the total quantity to display on the table A for the corresonding Year and Month, right?

    I have tested it on my local environment, we can get the total quantity in table B, and then use LOOKUPVALUE function to display it on table A. The DAX expression below is for you reference.
    =CALCULATE(SUM(case0630B[Quantity]),ALLEXCEPT(case0630B,case0630B[ProductNo],case0630B[Year],case0630B[Month]))
    =LOOKUPVALUE(case0630B[GroupQuantity],case0630B[ProductNo],case0630A[ProductNo],case0630B[Year],case0630A[Year],case0630B[Month],case0630A[Month])

    Reference
    https://msdn.microsoft.com/en-us/library/gg492170.aspx?f=255&MSPPError=-2147217396

    Regards,


    Charlie Liao
    TechNet Community Support

    • Marked as answer by SaigonCitizen Wednesday, July 1, 2015 9:25 AM
    Tuesday, June 30, 2015 6:47 AM

All replies

  • Hi SaigonCitizen,

    I suppose you don't have a relationship between Table 1 and Table 2. In this case, you can create a calculated column with the formula

    =CALCULATE(SUM(Table2[Receiving Quantity]);Table2[Year]=EARLIER([Year]);Table2[Month]=EARLIER([Month]);Table2[Product No]=EARLIER([Product No]))

    • Marked as answer by SaigonCitizen Wednesday, July 1, 2015 9:25 AM
    Monday, June 29, 2015 1:53 PM
    Answerer
  • Hi Saigon,

    According to your description, you need to get the total quantity to display on the table A for the corresonding Year and Month, right?

    I have tested it on my local environment, we can get the total quantity in table B, and then use LOOKUPVALUE function to display it on table A. The DAX expression below is for you reference.
    =CALCULATE(SUM(case0630B[Quantity]),ALLEXCEPT(case0630B,case0630B[ProductNo],case0630B[Year],case0630B[Month]))
    =LOOKUPVALUE(case0630B[GroupQuantity],case0630B[ProductNo],case0630A[ProductNo],case0630B[Year],case0630A[Year],case0630B[Month],case0630A[Month])

    Reference
    https://msdn.microsoft.com/en-us/library/gg492170.aspx?f=255&MSPPError=-2147217396

    Regards,


    Charlie Liao
    TechNet Community Support

    • Marked as answer by SaigonCitizen Wednesday, July 1, 2015 9:25 AM
    Tuesday, June 30, 2015 6:47 AM
  • Thank both of you very much. All are correct. It works. :)
    Wednesday, July 1, 2015 9:26 AM