Create a new table from existing one

[Wanted11235]

Hello!

I'm getting used to DAX on Power BI.

Below is an example of some of the columns from a query I have imported into Power BI. Notice allowance is different every year, where values are different every quarter

Ideally, this leads me to wanting the table below. Where I am grouping by year, quarter, SUM(VALUES) (for each quarter for that year), allowance

Anyone know how to do this?

Thanks!

[Iadem · Accepted solution]

Hello,

1) Create measure Sum_Values=Sum([Values])
2) add matrix/table visualisation and add the field how at screenshot
3) After that you need - right click at "Allowance" and click "W/o calculation"

[Chihiro · Accepted solution]

If for some reason you want to create another table (calculated) and not visual...

Add helper column to source table (Table1).

YRQ = [Year] & "-" & [Quarter]

Then in Modeling tab, add New Table

Table2 = DISTINCT(Table1[YRQ])

Build relationship between YRQ column of each table.

Add columns.

For Values:

Values = SUMX(FILTER(Table1,Table1[YRQ]=Table2[YRQ]),[Values])

For Allowance: (Same construct for Year & Quarter, just change the column reference for last argument)

Allowance = MINX(FILTER(Table1,Table1[YRQ]=Table2[YRQ]),Table1[Allowance])

[Wanted11235]

I have just tried this and works brilliantly! Will be very useful for me later

Thanks!

[Iadem · Accepted solution]

Hello,

1) Create measure Sum_Values=Sum([Values])
2) add matrix/table visualisation and add the field how at screenshot
3) After that you need - right click at "Allowance" and click "W/o calculation"

[Wanted11235]

Simpliest solutions seems to work the best 🙂

Thanks!