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
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

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,422)