cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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
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:

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.

4 REPLIES 4
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:

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.

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

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
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}})`

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors