cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
astrontelstar
Helper III
Helper III

Building Sales reports

I have a nice starter app going that stores each PO in my company.  I keep sales price and vendor price and calculate profit with a simple sales price - vendor price.

I would like to now generate some simple sales screens showing:

1. Sales by Vendor (top 10)

2.  Sales by Client (top 10)

3.  Profit by Customer (top 10)

 

Also, I realize powerBI might be the easiest, however I was hoping not to be licensed to death and able to do it with the native functions in Power Apps I'm already paying for?

Anyone with the best approach?

1 ACCEPTED SOLUTION

Accepted Solutions
rubin_boer
Super User
Super User

hi @astrontelstar you have a few steps to do.

 

I am going to make a few assumptions:

  • Purchase Order is unique (but it repeats for vendor, client, and profit)
  • you have a column for PO
  • you have a column for Vendor & Vendor Price
  • you have a column for Client & Client Price
  • you have a column for Customer Profit (client = customer)

 

PO | Vendor | Vendor Price | Client | Client Price | Customer Profit

The data will look something like this in a gallery (yours will differ, but the principle is the same)

I collected in a collection colProfit2

rubin_boer_1-1627330498166.png

 

Now let's create three collection for you to keep things simple. 

col1: colClientTotals

 

ClearCollect(colClientTotals,AddColumns(GroupBy(colProfit2, "Client", "Clients"),"Clients's Total Price", Sum(Clients,'Client Price')))

 

col2: colVendorTotals

 

ClearCollect(colVendorTotals,AddColumns(GroupBy(colProfit2, "Vendor", "Vendors"),"Vendor's Total Price", Sum(Vendors,'Vendor Price')))

 

col3: colCustomerProfits

ClearCollect(colCustomerProfits,AddColumns(GroupBy(colProfit2, "Client", "Profits"),"Clients's Total Profit", Sum(Profits,'Customer Profit')))

 

add 3 galleries with 2 labels each.

label1: descriptor

label2: total

Gallery to show clients, i created 4 distinct clients

Gallery Item = colClientTotals

rubin_boer_2-1627330861833.png

 

Gallery Item = colVendorTotals

rubin_boer_3-1627330944150.png

Gallery Item = colCustomerProfits

rubin_boer_4-1627330987034.png

To rank them, let ssee the top 3 for all (the collection is sorted in descending order so the first 3 entries are what we lookng for.

Top 3 Client Prices 

 FirstN(Sort(colClientTotals,'Clients''s Total Price',Descending),3)

rubin_boer_5-1627331042449.png

Top 3 Vendor Prices

 FirstN(Sort(colVendorTotals,'Vendors''s Total Price',Descending),3)

rubin_boer_7-1627331178482.png

Top 3 customer profits

 FirstN(Sort(colCustomerProfits,"Clients's Total Profit",Descending),3)

rubin_boer_8-1627331204877.png

 

Hope this helps

 

 

 

 

View solution in original post

4 REPLIES 4
KvB1
Solution Specialist
Solution Specialist

I would use a gallery with FirstN(Sort( to show the first 10 records of your list sorted by whichever metric you want to show. You could have a gallery for each metric, or have them all in one gallery and change the view based on a dropdown or something

astrontelstar
Helper III
Helper III

Still a little stuck; sorry.  So How can I get the table where it tallys each customer by adding up all their orders?  I don't want to have to write a function for each client.  Does that make sense?

 

rubin_boer
Super User
Super User

hi @astrontelstar you have a few steps to do.

 

I am going to make a few assumptions:

  • Purchase Order is unique (but it repeats for vendor, client, and profit)
  • you have a column for PO
  • you have a column for Vendor & Vendor Price
  • you have a column for Client & Client Price
  • you have a column for Customer Profit (client = customer)

 

PO | Vendor | Vendor Price | Client | Client Price | Customer Profit

The data will look something like this in a gallery (yours will differ, but the principle is the same)

I collected in a collection colProfit2

rubin_boer_1-1627330498166.png

 

Now let's create three collection for you to keep things simple. 

col1: colClientTotals

 

ClearCollect(colClientTotals,AddColumns(GroupBy(colProfit2, "Client", "Clients"),"Clients's Total Price", Sum(Clients,'Client Price')))

 

col2: colVendorTotals

 

ClearCollect(colVendorTotals,AddColumns(GroupBy(colProfit2, "Vendor", "Vendors"),"Vendor's Total Price", Sum(Vendors,'Vendor Price')))

 

col3: colCustomerProfits

ClearCollect(colCustomerProfits,AddColumns(GroupBy(colProfit2, "Client", "Profits"),"Clients's Total Profit", Sum(Profits,'Customer Profit')))

 

add 3 galleries with 2 labels each.

label1: descriptor

label2: total

Gallery to show clients, i created 4 distinct clients

Gallery Item = colClientTotals

rubin_boer_2-1627330861833.png

 

Gallery Item = colVendorTotals

rubin_boer_3-1627330944150.png

Gallery Item = colCustomerProfits

rubin_boer_4-1627330987034.png

To rank them, let ssee the top 3 for all (the collection is sorted in descending order so the first 3 entries are what we lookng for.

Top 3 Client Prices 

 FirstN(Sort(colClientTotals,'Clients''s Total Price',Descending),3)

rubin_boer_5-1627331042449.png

Top 3 Vendor Prices

 FirstN(Sort(colVendorTotals,'Vendors''s Total Price',Descending),3)

rubin_boer_7-1627331178482.png

Top 3 customer profits

 FirstN(Sort(colCustomerProfits,"Clients's Total Profit",Descending),3)

rubin_boer_8-1627331204877.png

 

Hope this helps

 

 

 

 

View solution in original post

Rubin,

I think I'm stuck with trying to figure out collections still to generate colProfit2.

When I do:

ClearCollect(newCollection,Table({Client:'Mprinted Business Operations'.Customer}))

Trying to build the first colProfit2 you mention, all I get in the collection is 1 record.

 

Any advice?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,327)