Need help to create a measure to calculate total of a specific field name from different columns

[abhishek_2593]

Hi,

I have a data set for various campaigns, some of which have multiple commercial partners.
How can i write a measure to calculate the total of these columns(commercial clicks) for each commercial partner type?

Data is as follows:

I will need to use this calculated totals to plot in a bar grapgh to show the click and click rates for each partner.

Can someone please advise?

[Fowmy · Accepted solution]

@abhishek_2593 

You need to trasnform this data in to a Power Bi friendly format, then use a simple measure like sum(yourtablename[clicks]) and use it with Partners.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"Total Clicks"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Campaign"}, "Attribute", "Value"),
    
    GroupedColumn = Table.AddColumn(#"Unpivoted Other Columns", "Grouped", each if Text.Contains([Attribute], "Clicks") then "Clicks" else "Partner"),
    #"Pivoted Column" = Table.Pivot(GroupedColumn, List.Distinct(GroupedColumn[Grouped]), "Grouped", "Value"),
    #"Filled Down" = Table.FillDown(#"Pivoted Column",{"Partner"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Clicks] <> null)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Clicks", type number}})
in
    #"Changed Type"

After Transformation 

[abhishek_2593]

Thank you for the help @Fowmy  😀