topic Re: Calculated table in DAX Commands and Tips

Calculated table

ChiRomeu — Mon, 12 Feb 2024 20:39:57 GMT

Hello colleagues,

I have a following table which I want convert it to:

Could you please kindly help me to solve that?

Best regards

Chi

Re: Calculated table

adudani — Mon, 12 Feb 2024 21:02:22 GMT

hi ChiRomeu ,

I used power query for this.

create a blank query, copy and paste the below code into the advanced editor.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlbSUTI0MjAwANIGhvpABBaM1QHLGgFFjUzQZI2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Amount = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Amount", type number}, {"Date", type date}}), //create a list of months Month = Table.AddColumn(#"Changed Type", "Month", each {1..12} ), //expand the months #"Expanded Month" = Table.ExpandListColumn(Month, "Month"), //create a date using year and month #"Added Custom" = Table.AddColumn(#"Expanded Month", "Date_", each #date([Year],[Month],01)), //transform to look like output #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Date"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date_", type date}, {"Month", Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Date_", "Date"}}) in #"Renamed Columns"

let me know if this works for you.

thanks.

Re: Calculated table

ChiRomeu — Tue, 13 Feb 2024 08:27:51 GMT

Dear Avinash,

It works a half until expanded column "month", but as you can see the picture above, i want to something like when expanded to month meanwhile divided by 12 for the amount column.

Thanks

Chi

Re: Calculated table

adudani — Tue, 13 Feb 2024 13:13:06 GMT

Hi ChiRomeu ,

Kindly add a column after the month lost step.

Use [amount] / List.Count([Month] in the step and then expand the month list.