How to add values of a column based on another column

[sankhadeep22]

Hi ALL,

State	Fuel Type	Count
WB	Dual	100
WB	Electricity	35
WB	Gas	20
BIH	Dual	115
BIH	Electricity	25
BIH	Gas	10
TN	Gas	40

I am Novice at Power BI. I have the above table. My requirement is as 

to add "dual" fuel values to both electricity and gas where there are dual connections else keep as is. I would only consider "gas" and "electricity" in the table format, which I would like to change into chart later. Below is the required table output

State	Fuel	Count
WB	Electricity	135
WB	Gas	120
BIH	Electricity	140
BIH	Gas	125
TN	Gas	40

Please help

Thanks

S

[v-jayw-msft]

Hi @sankhadeep22 ,

Have your problem be solved? Please consider accept the answer as a solution if it worked.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[v-jayw-msft · Accepted solution]

Hi @sankhadeep22 ,,

Create calculated column.

Column = 
var dual = CALCULATE(SUM('Table'[Count]),FILTER('Table','Table'[State]=EARLIER('Table'[State])&&'Table'[Fuel Type]="Dual"))
return
IF('Table'[Fuel Type]="Dual",'Table'[Count],'Table'[Count]+dual)

 

Create calculated table.

Table 2 = FILTER('Table','Table'[Fuel Type]<>"Dual")

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Mariusz]

Hi @sankhadeep22 

Please see the attached file with a solution using Power Query

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Mariusz]

Hi @sankhadeep22 

Sure, just paste this into QE Blank Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCndS0lFyKU3MAVKGBgZKsTpQMdec1OSSoszkzJJKIM/YFCHjnlgMJI0gap08PZAMMDRFEkQ1wQhZCmKEIcSIED+4iAlQJBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [State = _t, #"Fuel Type" = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"State", type text}, {"Fuel Type", type text}, {"Count", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"Fuel Type"]), "Fuel Type", "Count", List.Sum),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"Dual", "Electricity"}),
    #"Added Custom" = Table.AddColumn( #"Replaced Value", "Electricity ", each [Electricity] + [Dual], Int64.Type ),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Gas ", each [Gas] + [Dual], Int64.Type ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"State", "Electricity ", "Gas "}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Electricity ", "Electricity"}, {"Gas ", "Gas"}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"State"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> 0))
in
    #"Filtered Rows"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[sankhadeep22]

Hi @Mariusz ,

When I open the pbix file , it shows "Unable to open the document - the queries were authored using a newer version of desktop and might not work with your version" .

I am using version 2.75 of desktop..

Can you provide me the solution in a reply?

Thanks ,

S