This is a very simple thing I am trying to do, but for the life of me I can't understand how DAX does it. I want to find, say an average ( or any calculation really) of a column where certain other column values are the same. Below is a simple example using real estate auction prices - I want to find the average price of each real estate based for each real estate type (house, unit etc.) and suburb.
This is what I have so far.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVS7asNAEPwVI1Juca99XOlULgNJZ1w4iUgMsmRkheC/zyo4jqU7JBUqDoaZ2Z1ZbbfF+rRvu2NZdwUUz6f2UH+sNoeq0tfqwYJxCMbySp/FDobo9fmt6X5xhA5sxBtq03ydywECvUBEe0M8tc2pbLtLD6rey7YqL1dFRARCP8FlwZmgrkKOLR0BHYMI5cCP7eH8uq/LP1qyQC6VHsGsEf1o0iH5CBT97Lys+3VibriX5rv+zMsyqnSUmSh6aQFBmbRnrAZmszsZ2vOsupS1l27aOQfRmVR5mLB4C8FJlvTfpDACUphBabyWIbCZjZeJ+hXOhRsMgo/T4UYbwS4qs+hhsIR8ZkMohQB816p76MijGE2P0oTHiVBw4HG+hMooAn4Bo7ZQZ1erSwprqe82zhbW6zhBpi9eDEGk7CipRx89BMpvfeCRUP8jMVVOKaMPenxLIncmguH0CMacHESvwC+pBvenwFfx3Q8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Real Estate Type" = _t, Suburb = _t, #"Auction Price" = _t, #"Average for House Type and Suburb" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Real Estate Type", type text}, {"Suburb", type text}, {"Auction Price", Currency.Type}, {"Average for House Type and Suburb", type text}})
in
#"Changed Type"
And this is what I want the avergae column to look like. I found these values by filtering each combination of real estate types and suburbs and then finding the average when filtered (in Excel).
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZRBb9swDIX/ShH0yINIihR17E49DuhuRQ7dZqwBsiRwMhT996OQqJVr13VONuHvydR7Eh8fV3eHp/70t9udVrC62/7u+m336q+3CBojpKDnImEA5Lxaw5wkJQFJOJbc7/8duxFujBDJhkXBv/f7Q9efXkcKEQEVPhdMBig0p0gkQBaW85wgiS7mvW0hSBavUZgBq12hoJAhpIldDJI4/tqX5616R5jlTGNOYFE+pz1nNt+FLeY5EETjMf8WcmXFV85yOQ4s7DKcJsseIwSMi1jlDJp5ERvQ3UBdxGbMbu2yfi0oZJ3o4cf+Zff8kbdyMTRWzxQE0+c0ls8Qa+It32byrd8cfz7tutqQb7QeKyqHhHFWouKOVxdbQd3tcH0MTtRxQKJ+T+bwGAQ4T+DNKR8qSD1Z4hpbBLQ0p0h6XnTEt54Of+G4u3nxKIoBRfpCglomiIwlra0Ph36z+3Nzv9luL+343Ake2aBozfqg0EjlEL0bgV7M8GW65jKhr1BkdosyLlakaD6QJ3pq8hj9RMjngV1ijxpAk3wp4uzXp96OVtTGMlIREWQKw2K9/g8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Real Estate Type" = _t, Suburb = _t, #"Auction Price" = _t, #"Average for House Type and Suburb" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Real Estate Type", type text}, {"Suburb", type text}, {"Auction Price", Currency.Type}, {"Average for House Type and Suburb", Currency.Type}})
in
#"Changed Type"
