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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.