Seggregate a measure value based on a category

[karun_r]

I have a dataset which has a numerical column which mentions number of purchases made. Along with this, I do have the month in which the purchase is made and also the customer who did the purchase.

Now I have something like 5 years worth of data with me and I need to come up with a measure which will display how many purchases were made by each customer in each month.

So if the data is something like

 Date          Category   Purchases                  Customer

1/1/2015        A                 2                                  1

1/1/2015        B                 3                                  1

1/2/2015        A                 1                                  1

1/2/2015        B                 5                                  1

1/1/2015         A               1                                   2

I need the final output to be

Date           Purchases           Customer

1/1/2015          5                        1

1/1/2015          1                        2

1/2/2015          6                        1

I have already aggregated the purchases by month using the measure below

CALCULATE(SUM([Purchases]), FILTER(ALL(Table), ([Date]) = (MAX([Date]))))

I am using this measure inside a calculated column on the table above and this is giving me the total purchases made in a month irrespective of the customer. Now how do I further slice it down to the customer level ?

[Ashish_Mathur]

Hi,

Build a simple visual.  Drag the Customer and Date to the Table visual.  Write this measure

=SUM(Data[Purchases])

Hope this helps.

[karun_r]

No that won't work as I need to do further calculation on this so I am doing this inside the model. Once I have the information on this, I need to create a new flag based on some other conditions.

[Ashish_Mathur]

Hi,

It is possible to create this as a table.  Please descibe your entire question so that the appropriate solution can be shared.

[karun_r]

I don't think creating a new table will do the job here. Because, right now, with the file as is, it is taking huge time to calculate one measure. Anyway, this is what I wanna do:

I have a table with data with below fields:

1. Date

2. Customer ID

3. Product Category I

4. Product Category II

5. Product Category III

6. #Units purchased by each customer.

All the product category columns are part of product category hierearchy.

So now I would like to add new column to this existing table which will have the total number of units purchased by the customer in that month. If a row has data about  the purchases made by the customer Acme Corp. in a product category Bikes ( Vehicles -> Two Wheelers -> Bikes)  in the month of June,2015 then this new column will have the total purchases made by Acme Corp. in the month of June irrespective of the product category.

Does this explain the scenario better ?

[v-huizhn-msft]

Hi @karun_r,

Have you resolved your issue? If you have, please mark the right/helpful reply as answer. More people will learn new things here.  If you haven't, please feel free to ask.

Best Regards,
Angelia

[MarkLaf]

The below is achieved by selecting the Table visualization, adding all columns shown, and using the following formula for Measure:

Measure = CALCULATE(SUM('Table'[Purchases]),ALLEXCEPT('Table','Table'[Date],'Table'[Customer]))

I get that you want to do additional work once you have this number. The point to keep in mind is that you can do this with measures. For a simple example (note that in this new measure, I'm referencing the first):

Product % = SUM('Table'[Purchases])/[Measure]

Is that going in the direction you're looking for?

[MarkLaf]

So, you actually need to create a new, aggregated table in your model? Click the New Table button and use the below:

Table2 =
ADDCOLUMNS(
    SUMMARIZE('Table','Table'[Date],'Table'[Customer]),
    "Purchases",CALCULATE(
        SUM('Table'[Purchases]),
        FILTER('Table',
            'Table'[Date]=EARLIER('Table'[Date]) && 
            'Table'[Customer]=EARLIER('Table'[Customer])
        )
    )
)

Or, if you really just need it for additional calculations on your original table, it may make more sense to just use the above in your measure/calculated column. I'd suggest putting it in a variable.

I'll also note, that if this is the table you really need, and not the original, you may consider using the Group By function in the query editor and starting with the data you need instead of adding unnecessary tables to your model.

[MarkLaf · Accepted solution]

Actually, I was thrown off by your formula earlier because it's giving you the total purchases for the last month, not actually disagregating by every month, so didn't get what you were going for. But I get it now (I think). The following will work in a calculated column:

CalcColumn=
CALCULATE(
    SUM('Table'[Purchases]), 
    FILTER('Table', 
        'Table'[Date] = EARLIER('Table'[Date]) && 
        'Table'[Customer] = EARLIER('Table'[Customer])
    )
)

All that said, it sounds like this would be more appropriate to go in a measure. I.e. if you want to know for each month and customer what the sum purchases were regardless of category (because you're going for some category percentage or something), then the measure would simply be the following, which you can easily reuse in other measures:

Measure = CALCULATE(SUM('Table'[Purchases]),ALL('Table'[Category]))

[karun_r]

But how does the second Measure that you mentioned is going to factor in the customer grouping and the month grouping ?

[MarkLaf]

Maybe it would be helpful to describe one of the flags you are ultimately going for?

I think the reason your function using && wasn't working before is that it's pretty easy to run into errors if you don't always specify the table with the column. E.g. Table[Purchases] in Excel PowerPivot or 'Table'[Purchases] in PBI Desktop. Also, you wanted to use EARLIER instead of MAX, but I don't see how that would affect performance (I imagine MAX is faster). If you are using Excel, another reason for the hangup could have been an error message popped up after a change in PowerPivot that locked all of Excel - if you don't notice the popup and go back to Excel proper to close it, it can appear like PowerPivot is stuck.

[karun_r]

I actually did check for the error popups on the main window of the Excel as I encountered this kind of thing before but there was no pop up of errors/warnings.

The status bar on the bottom right corner was saying "Calculating.." and then at the midway, it stopped moving and after some time (20 mins approx) the Excel application will crash. This happens everytime I apply the formula including both Customer and Data filters using &&

[karun_r]

I just wanted a intermediate column so that I can calculate my other flags based off on this. I actually tried using the && in the FILTER function earlier but it was taking way too much time like 30 minutes but it was not calculating the value. The formula that I used is:

AggregatedMeasure :=
CALCULATE (
    SUM ( [Purchases] ),
    FILTER (
        ALL ( Table ),
        ( Table[Date] = MAX ( Table[Date] ) )
            && [Customer] = MAX ( Table[Customer] )
    )
)