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

[nok]

Hi!

I have a table that follows this structure:

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

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:

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

How can I do this?

[Ashish_Mathur]

Hi,

If you are OK with a measure based solution, then write these measures

Total = sum(Data[Balance])

C = calculate(countrows(Data),data[type]="A")

D = max(Data[Date])

Hope this helps.

[Gabry · Accepted solution]

Hey,

try to make a calculated table with this dax

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