Forum Discussion

sneevand's avatar
sneevand
Frequent Visitor
5 years ago
Solved

Each Customer Total Rollup

YearValue MonthValue CustomerNo Qty 2019 1 1 10 2019 2 1 5 2019 3 2 20 2020 1 3 20 2020 2 2 5 2020 3 1 2 2020 1 1 10   We want to group by custome...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

  • 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

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