Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
VinnyH
Frequent Visitor

Single Sum of Sales for Multiple Customers

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.

1 ACCEPTED SOLUTION
VinnyH
Frequent Visitor

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:

RANKKKK.PNG

 

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.
 

View solution in original post

4 REPLIES 4
VinnyH
Frequent Visitor

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:

RANKKKK.PNG

 

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
Frequent Visitor

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

Revenue.PNG 

 

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.

revenue 2.PNG

Anonymous
Not applicable

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
Resolver III
Resolver III

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors