Perform a double aggregate with DAX

[AndersDonker]

Dear forum,

i'm struggling with a solution which I expect to be easy but can't seem to wrap my head around within DAX.

My desired output is to have a dynamic calculation that can be filtered in PowerBI with the regular relationships and underlying data but is aggregated twice.

The first aggregate is to calculate how many products a single customer has taken, given a specific timeframe and possible other filters.

The second aggregate is to group these customers into clusters to have an output similar to the following:

Desired output:

# Amount of customers	# Amount of products taken
25.421	0 Products
49.572	1 to 3 Products
12.451	4 to 6 Products
6.021	7 to 9 Products
3.212	10 to 12 Products
1.421	> 12 Products

How do I generate a measure (and maybe some additional columns) to create such an output?

I have added an example dataset to this topic to experiment with.

In a second question, i would like to also be able to aggregate the data into years since a customer has entered vs the product taken date. An example of this output would be:

# amount of products taken	Years since customer start
1503	0
232	1
24	2
10	3

Datamodel example:

Example data

CustomerID	Customer_Since
A	1-1-2021
B	1-2-2021
C	1-2-2022
D	1-6-2022
E	1-8-2022
F	1-10-2022
G	1-12-2023

Example Product data:

ID	ProductID	CustomerID	Product_Date
1	1	A	3-1-2021
2	2	A	12-1-2023
3	3	B	1-5-2021
4	3	B	1-10-2021
5	6	C	1-1-2024
6	10	E	1-6-2023
7	13	E	1-8-2023
8	15	E	1-10-2023
9	18	E	1-12-2022
10	1	F	1-6-2023
11	21	F	1-8-2023

Note: the desired output is just fictive, it does not correspond with the provided example data.

The link to the example PBIX dataset:

Example-Set.pbix

[AndersDonker · Accepted solution]

Hi @v-nuoc-msft,

i have added a note and i added the pbix file. 

[ERD · Accepted solution]

Hi @AndersDonker ,

The answer to your first question:

1. You need a new table with categories and a Date table:

2. Measures:

products amt = DISTINCTCOUNT(Product2[ProductID])

customers amt = 
VAR c_category = SELECTEDVALUE ( Categories[Amt category] )
VAR t =
    FILTER (
        ADDCOLUMNS (
            VALUES ( Customer1[CustomerID] ),
            "@products", [products amt],
            "@category",
                VAR amt = [products amt]
                RETURN
                    CALCULATE (
                        MAX ( Categories[Amt category] ),
                        amt <= Categories[MaxValue],
                        amt >= Categories[MinValue]
                    )
        ),
        [@category] = c_category
    )
RETURN
    COUNTAX ( t, [CustomerID] )

The answer for your second question:

1. You need a new table with possible years:

2. Measure:

products taken = 
VAR customers = SUMMARIZE ( Customer1, Customer1[CustomerID], Customer1[Date_Since] )
VAR customers_with_dt_from =
    SELECTCOLUMNS (
        GENERATE (
            customers,
            VAR dt = [Date_Since]
            VAR m = MONTH ( dt )
            VAR d = DAY ( dt )
            RETURN
                FILTER (
                    GENERATESERIES ( dt, DATE ( YEAR ( TODAY () ), m, d ), 1 ),
                    DAY ( [Value] ) = d && MONTH ( [Value] ) = m
                )
        ),
        "CustomerID", [CustomerID],
        "dt_from", [Value]
    )
VAR customers_with_date_to =
    ADDCOLUMNS (
        customers_with_dt_from,
        "dt_to",
            VAR dt = [dt_from]
            VAR dates = DATESINPERIOD ( 'Date'[Date], dt, 12, MONTH )
            RETURN
                MAXX ( dates, [Date] )
    )
VAR customers_with_products =
    ADDCOLUMNS (
        customers_with_date_to,
        "years_since",
            VAR _customer = [CustomerID]
            VAR min_dt = MINX ( FILTER ( customers_with_date_to, [CustomerID] = _customer ), [dt_from] )
            VAR max_dt = [dt_to]
            RETURN
                DATEDIFF ( min_dt, max_dt, YEAR ),
        "products",
            VAR c_dt_from = [dt_from]
            VAR c_dt_to = [dt_to]
            RETURN
                CALCULATE (
                    [products amt],
                    Product2[Product_Date] >= c_dt_from,
                    Product2[Product_Date] <= c_dt_to
                )
    )
VAR _years = SELECTEDVALUE( Years[Years] )
VAR result = SUMX( FILTER( customers_with_products, [years_since] = _years), [products])
RETURN
    result

[AndersDonker]

Hi @ERD, now that i am trying to implement your solution regarding the customer amt into my existing model I seem to have an issue with the VALUES() part of the solution. I would like to (pre)filter the customers based on their date_since. Is there an option to extend the solution with this feature?

customers amt = 
VAR c_category = SELECTEDVALUE ( Categories[Amt category] )
VAR t =
    FILTER (
        ADDCOLUMNS (
            VALUES ( Customer1[CustomerID] ),
            "@products", [products amt],
            "@category",
                VAR amt = [products amt]
                RETURN
                    CALCULATE (
                        MAX ( Categories[Amt category] ),
                        amt <= Categories[MaxValue],
                        amt >= Categories[MinValue]
                    )
        ),
        [@category] = c_category
    )
RETURN
    COUNTAX ( t, [CustomerID] )

On line 6 you use the VALUES ( Customer1[CustomerID] ) solution. Is it possible to extend this part of the code to filter the customers we process in the measure, based on date?

For example: if i choose the year 2021, I would like to see that the measure returns just 2 customers (A and B) and for both produce a 1 as a result in the category 1 to 3 products. I don't want to see C D E F G as customers with 0 products.

I am thinking of something like:
FILTER(VALUES(Customer1[CustomerID]),  with date between min and maxdate of the year) but that obiously does not work. 🙂

[AndersDonker]

Amazing! many thanks for your elaborate solution to the problem! I now understand why I was unable to produce the results myself. I need to increase my understanding of DAX to a more indepth level to grasp these type of solutions!

Thanks!

[v-nuoc-msft]

Hi @AndersDonker

I can't seem to understand your desired outcome. Can you provide your PBIX file? So that I can help you better. Please remove any sensitive data in advance.

Regards,

Nono Chen

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

[AndersDonker · Accepted solution]

Hi @v-nuoc-msft,

i have added a note and i added the pbix file.