Create a table with unique IDs and calculations based on the original table

Question

Hi!I have a table that follows this structure:IDBalance&nbsp; &nbsp;Type&nbsp; &nbsp;Date111&nbsp; &nbsp;10A20/10/2025&nbsp;&nbsp;11120M30/10/20252225A02/12/20252223B01/09/202433340M23/11/202433330M27/05/202533321A21/09/20254449B17/03/2025&nbsp;I want to create a new calculated table based on the original that displays distinct IDs, sum of Balance for each ID, columns with the count of rows for each type, and the maximum date for each ID and type.The end result would be a table like this:IDBalance&nbsp;&nbsp;Count(Type A)&nbsp; &nbsp;Max Date(Type A)Count(Type B)&nbsp; &nbsp;Max Date(Type B)Count(Type M)&nbsp; &nbsp;Max Date(Type M)111&nbsp; &nbsp;30120/10/2025&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;0&nbsp;130/10/2025&nbsp; &nbsp;2228102/12/2025101/09/2024&nbsp; &nbsp;&nbsp;0&nbsp;33371121/09/20250&nbsp;227/05/202544490&nbsp;117/03/20250&nbsp;How can I do this?

gabry · Accepted Answer

Hey,
try to make a calculated table with this dax
&nbsp;
SummaryByID :=
ADDCOLUMNS(
    SUMMARIZE(
        'Fact',
        'Fact'[ID],
        "Balance", CALCULATE(SUM('Fact'[Balance]))
    ),
    // Type A
    "Count (Type A)",     CALCULATE(COUNTROWS('Fact'), 'Fact'[Type] = "A"),
    "Max Date (Type A)",  CALCULATE(MAX('Fact'[Date]), 'Fact'[Type] = "A"),
    // Type B
    "Count (Type B)",     CALCULATE(COUNTROWS('Fact'), 'Fact'[Type] = "B"),
    "Max Date (Type B)",  CALCULATE(MAX('Fact'[Date]), 'Fact'[Type] = "B"),
    // Type M
    "Count (Type M)",     CALCULATE(COUNTROWS('Fact'), 'Fact'[Type] = "M"),
    "Max Date (Type M)",  CALCULATE(MAX('Fact'[Date]), 'Fact'[Type] = "M")
)

Forum Discussion

Create a table with unique IDs and calculations based on the original table

2 Replies

Related content

Create Origin To Destination Map In BI Desktop

Need help to create Calculation in DAX

Create calculated row

Semantic model - create calculated column

Creating ICMs via Activator

ID	Balance	Type	Date
111	10	A	20/10/2025
111	20	M	30/10/2025
222	5	A	02/12/2025
222	3	B	01/09/2024
333	40	M	23/11/2024
333	30	M	27/05/2025
333	21	A	21/09/2025
444	9	B	17/03/2025

ID	Balance	Count (Type A)	Max Date (Type A)	Count (Type B)	Max Date (Type B)	Count (Type M)	Max Date (Type M)
111	30	1	20/10/2025	0		1	30/10/2025
222	8	1	02/12/2025	1	01/09/2024	0
333	71	1	21/09/2025	0		2	27/05/2025
444	9	0		1	17/03/2025	0