Editing Table Visual - Combining Rows Based on Location

[W2SANC]

Hi! 

I have a visual - table with three simple columns (see below). 

Column 1: Total Sales ($) 

Column 2: County

Column 3: State 

My data in the power query is set up so that each row is a transaction/sale. This is caucusing the table to show (for example) 30 sales of varying amounts for Allen County, Indiana. I want it to show the TOTAL PER COUNTY / STATE so that you don't have to manually add up all of the individual sale amounts. 

What is happening (fake numbers for example sake): 

$24,012.02      Allen      Indiana 

$3,023.36        Allen      Indiana 

$97.04             Allen      Indiana 

What I want the colums to reflect: 

$27,123.42      Allen      Indiana

When looking at my modeling tab, I have a connection already between the county/state query I have to my data set for another variable (population size), and it says I can't add another active relationship. What should I do to resolve this? 

Thanks in advance for any help.

[Anonymous · Accepted solution]

Hi @W2SANC ,

If you want to get the results from the first table in the second data table, then try the Group By function.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUjEy0TEwNNIzMFLSUXLMyUnNUwADIM8zLyUzMS9RQSlWB6jOWMfAyFjP2IyAMktzPQMTQmosgEJGxhACLGJqhCwSCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"total sales" = _t, County = _t, State = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"total sales", Currency.Type}, {"County", type text}, {"State", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"County", "State"}, {{"Count", each List.Sum([total sales]), type nullable number}})
in
    #"Grouped Rows"

About you can't add another active relationship: 

When Power BI Desktop automatically creates relationships, it sometimes encounters more than one relationship between two tables. When this situation happens, only one of the relationships is set to be active. The active relationship serves as the default relationship, so that when you choose fields from two different tables, Power BI Desktop can automatically create a visualization for you. However, in some cases the automatically selected relationship can be wrong. Use the Manage relationships dialog box to set a relationship as active or inactive, or set the active relationship in the Edit relationship dialog box.

More details: 

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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