Measure to check one column for a condition, then sum the rest of another column

[redalert787]

Hello, 

I am trying to build a DAX equation to help show some data in a way the database is not set up. 

Here is the context, I have 2 columns: 1)Old/New 2)Special/Range

What I would like to do is create a dax condition that shows me all the OLD then of the new how much is special/range. 

What my database currently looks like is something like this: 

Ideally what I want to do is create it so that it looks like the below to create 

To create the charts that look like this : 

[v-yueyunzh-msft · Accepted solution]

Hi , @redalert787 

According to your description, you want to convert the table , for your need , it can be realized easiler in Power Query Editor.

Here are the steps you can refer to :
(1)My test data is the same as yours.

(2)We can put this M code in the Advanced Editor" in Power Query Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wys9JUdJRCi5ITc5MzAGyDA0MlGJ1YOJFiXnpqUiifqnlJIpimoxVPBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"OLD/new" = _t, #"Special/range" = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OLD/new", type text}, {"Special/range", type text}, {"Sales", Int64.Type}}),
    #"New Table" = Table.RenameColumns(Table.RemoveColumns(Table.SelectRows(#"Changed Type", each ([#"OLD/new"] = "New")),{"OLD/new"}),{{"Special/range", "Column"}}),
    #"Old Table" = Table.RenameColumns(Table.RemoveColumns(Table.SelectRows(#"Changed Type", each ([#"OLD/new"] = "old")),{"Special/range"}),{{"OLD/new", "Column"}}),
    Custom1 = #"Old Table" & #"New Table"
in
    Custom1

(3)Then we can get the table you need:

(4)Then we can apply the data to Desktop, and we put the fields on the visual and we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[johnt75]

You could create a new conditional column in Power Query. If the old/new column equals old then use that column else use the special/range column. You can then include this column in your visuals with a measure summing sales.