Perform a double aggregate with DAX
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
