Each Customer Total Rollup

[sneevand]

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 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

CustomerNo	YearValue	TotalQty
1	2020	12
2	2020	5
3	2020	0

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-deddai1-msft]

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]

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 · Accepted solution]

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

[PhilipTreacy]

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]

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..