Forum Discussion

sneevand's avatar
sneevand
Frequent Visitor
5 years ago
Solved

Each Customer Total Rollup

YearValueMonthValueCustomerNoQty
20191110
2019215
20193220
20201320
2020225
2020312
20201110

 

We want to group by customer, which provide total as below; if the customer dont have value in previous year for a particualr moth or date and  ignore in total and default the value to zero, Measured is preffered choice.

 

Expected Output

CustomerNoYearValueTotalQty
1202012
220205
320200

 

 

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi sneevand ,

     

    You can use the following measure for instead:

     

    Measure =
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[CustomerNo],
        'Table'[YearValue],
        "Total Qty",
            IF (
                CALCULATE (
                    SUM ( 'Table'[Qty] ),
                    FILTER (
                        ALLEXCEPT ( 'Table', 'Table'[CustomerNo] ),
                        'Table'[YearValue]
                            = MAX ( 'Table'[YearValue] ) - 1
                    )
                ) = 0,
                0,
                CALCULATE (
                    SUM ( 'Table'[Qty] ),
                    FILTER (
                        ALLEXCEPT ( 'Table', 'Table'[CustomerNo] ),
                        'Table'[YearValue] = MAX ( 'Table'[YearValue] )
                    )
                )
            )
    ),
    [Total Qty]
)

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

6 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi sneevand 

    Sorry I'm not following the logic for your expected output.  You say if the customer dont have value in previous year for a particualr moth or date and  ignore in total and default the value to zero  

    So with Customer 1 in 2020 they have values for Jan and Mar,but in 2019 they only have a value for Jan, so why is the 2020 total 12? Shouldn't the Mar 2020 value be treated as 0?

    Same thing for Customer 2?

    Regards

    Phil

    • sneevand's avatar
      sneevand
      Frequent Visitor
      5 years ago

      corrected the data, Customer 3 dont have value for 2019 so it marked as 0 but customer 1,2 are having values in 2019 so need to do roll up for that year selected in filter like 2020, suppose i want 2019 then i will verify with 2018 data and get totals and  so on..

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi sneevand ,

     

    You can use the following measure:

     

    Measure =
IF (
    CALCULATE (
        SUM ( 'Table'[Qty] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[CustomerNo] ),
            'Table'[YearValue]
                = MAX ( 'Table'[YearValue] ) - 1
        )
    ) = 0,
    0,
    CALCULATE (
        SUM ( 'Table'[Qty] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[CustomerNo] ),
            'Table'[YearValue] = MAX ( 'Table'[YearValue] )
        )
    )
)

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

    • sneevand's avatar
      sneevand
      Frequent Visitor
      5 years ago

      Check the total instead of 17 it shows as  37 ; and it does not work simirally if i join with date dimension; my date dimension dates from min and max date of the customer table.

       

      i updated the code to use dates from date dimension,

      filter(ALLEXCEPT(Dates,Dates[Date]),Dates[MonthValue]=validmonth && Dates[YearValue]=validyear)

       and did sumx(values(customertable),measure) , but total wont match.

       

      • v-deddai1-msft's avatar
        v-deddai1-msft
        Community Support
        5 years ago

        Hi sneevand ,

         

        You can use the following measure for instead:

         

        Measure =
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[CustomerNo],
        'Table'[YearValue],
        "Total Qty",
            IF (
                CALCULATE (
                    SUM ( 'Table'[Qty] ),
                    FILTER (
                        ALLEXCEPT ( 'Table', 'Table'[CustomerNo] ),
                        'Table'[YearValue]
                            = MAX ( 'Table'[YearValue] ) - 1
                    )
                ) = 0,
                0,
                CALCULATE (
                    SUM ( 'Table'[Qty] ),
                    FILTER (
                        ALLEXCEPT ( 'Table', 'Table'[CustomerNo] ),
                        'Table'[YearValue] = MAX ( 'Table'[YearValue] )
                    )
                )
            )
    ),
    [Total Qty]
)

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

         

        Best Regards,

        Dedmon Dai