Expenses in % of sales

[gleblanc]

Hi,

I have a single table with all the general ledger transactions, listed by GL account number, with date, amounts, description, etc. In a pivot table, I can easily summarize it by account, date and vendors. I also have a related table (related by account number) that allow me to regroup accounts by type (balance sheet or income statement), category (revenues or expenses) and sub-category (gross sales, discounts, labour expenses, freight expenses, etc.).

What I need to do is to calculate the "% of sales" ratio of expenses but I can't get to work a measure with SUMX, ALLEXCEPT, FILTER, ALL or CALCULATETABLE... i'm getting a bit desperate.

What I found so far on the internet is something like "sales / total of sales"... what I need is something:

_________________ Amount _____% of net sales
Gross sales________1000.00______ 100.00%
Discounts___________-90.00______ -9.00%
Freight expenses_____-50.00_______ -5.00%

I need to be able to filter it by date and sub-category.

Thanks in advance!

 

[JohnCas]

Hi Beblanc,try this formula ex: =freight/GrossSales. and drag it down. make sure in formula lock the GrossSales cells. so that it would devide the ratio of ur expenses from ur total gross.

Hi,

I have a single table with all the general ledger transactions, listed by GL account number, with date, amounts, description, etc. In a pivot table, I can easily summarize it by account, date and vendors. I also have a related table (related by account number) that allow me to regroup accounts by type (balance sheet or income statement), category (revenues or expenses) and sub-category (gross sales, discounts, labour expenses, freight expenses, etc.).

What I need to do is to calculate the "% of sales" ratio of expenses but I can't get to work a measure with SUMX, ALLEXCEPT, FILTER, ALL or CALCULATETABLE... i'm getting a bit desperate.

What I found so far on the internet is something like "sales / total of sales"... what I need is something:

_________________ Amount _____% of net sales
Gross sales________1000.00______ 100.00%
Discounts___________-90.00______ -9.00%
Freight expenses_____-50.00_______ -5.00%

I need to be able to filter it by date and sub-category.

Thanks in advance!