Single Sum of Sales for Multiple Customers

[VinnyH]

Hi,

So I'm trying to get a sum of sales for multiple customers all put into one single line.

I have two tables in my dataset: One with multiple entries of each customer representing a single transaction each. I then also have a table with duplicates of customers removed.

so:

Customer:             Sales

A                               £100

B                               £90

A                               £45

C                               £100

D                               £30

A                               £250

B                               £40

Second Table:

A                               Total

B                                Total

C                                Total

D                                Total

When I try a simple SUM(Table[Sales]) formula to create a measured column showing the total in the second table - I have the total of sales regardless of customer populate into each line, and not the total for each customer respectively.

Sorry if this is a silly question - but I've used power bi so much the last few weeks I think a part of my brain has melted.

Thanks in advance for any help.

[VinnyH · Accepted solution]

I think I've managed to work this one out now, and I'll detail what I've done for the sake of anyone else that has this problem in future:

I created the measure as before: 

Revenue = SUM(AllMonths[Revenue])

Followed by a measured column:

Revenue2 = IF(HASONEVALUE('AllMonths'[Reseller Name]),[Revenue],

SUMX(VALUES('AllMonths'[Reseller Name]),[Revenue]))

My main reasoning for having set as a measured column is for flexibility for use in other parts of my report.

I could then use:

Rank = RANKX(ALL('Customers'[Reseller Name]),[Revenue])

to apply to my table visual to get an accurate ranking as shown in the above image.

Thank you for the input in attempts to help with this problem, it is really appreciated.

[VinnyH]

I think I might have used some incorrect terminology when explaining my problem. I'm trying to complete my work on a table in the data, not a table visualisation on a report. Thank you for the help though.

When I create a measure to sum the revenue, and then subsequently use RANKX; I recieve a total of revenue accross all lines, and not split by individual customer:

Revenue Measure=SUM('AllMonths'[Revenue])

Rank Measure=RANKX(ALL('AllMonths'[CustomerID]),[Revenue Measure])

This also results in every line being ranked as #1

 

Each different colour in the image below represents a different customer. I'm trying to have it so that there is only one line for each customer, with a sum of it's revenue accross the multiple lines. I'm then going to try and rank the customers by their revenue.

[Anonymous]

Insert new font, get table you want sum, click on group by, will open another window modify to add the column you want sum.

= Table.Group(#"Changed Type", {"Client"}, {{"Sum", each List.Sum([Value_Sum]), type number}})

---

---

[PANDAmonium]

It sounds like you might be creating a new column instead of a new measure. You shouldn't have to do either of that though. Your first table probably has something like Transaction ID, Customer Name, Sales. So your second table should just be the Customer Name and Sales column summarized as sum.