How on earth to calculate Percent of Sales for different intervals of time?

[jacob42volt]

Hi All, first thank you for reading and lending your help.

I have a table with 3 columns:

1) Product name

2) Invoice date

3) Sale amount

And each row of the table is a separate sale transaction.

My visual is Percent of Sales vs Time. There is a filter where the user can click on different products to populate the graph.

I am trying to calculate each product's percent of sales so that it is accurate in the monthly view, quarterly view, and yearly view. 

Here are my understandings:

• I need a calculated column to accomplish this because I don't want to create separate measures for every single product; far too many products
• I can make an accurate "monthly" calculation using 2 calculated columns: (1) For each row, identifies month of invoice date and returns that month's total sales. So for a given month, every row has the same number - the month's total sales. (2)  Percent of month's total sales, calculated by dividing each row's (each sale transaction) by the first column, effectively giving each transaction's percent contribution to month's total sales. Thus summing up these percentages (i.e. rolling up all of a particular product's transactions in a month), which the visual does, gives me each product's percent of sales across the monthly view, but obviously won't when rolled up to yearly/quarterly.
• Trying to use the "Show as percent of Grand Total" option on a calc never seems to work since my table is a mass data export that goes back many years

Conceptionally what I need is a calculated column that divides each row's "sale amount" by a dynamically changing denominator - total month's sales, quarter sales, yearly sales. Then summing up all those percentages in the given time frame gets me what I need.

Not sure if I'm thinking of this the right way. I'm new to DAX so if I'm completely off base and you could point me in the right direction I'd much appreciate it. All in all I just want a single calc I can drag into the visual for this, not hundreds of different calcs.

Thank you

Jacob

[johnt75]

You don't want to do it in calculated columns, you want a measure which will respond to different selections of products and dates. 

Firstly, you'll want a proper date table marked as a date table. Make sure that it has all the columns you might want to use for slicing and dicing, like year, month, quarter etc. Link this in a one-to-many relationship to your data table.

You'll also probably want a dimension table which just contains the product names. If you don't already have one you can create one like

Dim Product = DISTINCT( 'Table'[Product name])

Link this to your data table in a one-to-many relationship, and always use the column from this table in your visuals, don't use the column from the data table.

For the % of sales you can create a measure like

Percent of total sales =
VAR SalesCurrentProduct =
    SUM ( 'Table'[Sales] )
VAR SalesAllProducts =
    CALCULATE ( SUM ( 'Table'[Sales] ), REMOVEFILTERS ( 'Dim Product' ) )
RETURN
    DIVIDE ( SalesCurrentProduct, SalesAllProducts )

Add that to a visual with a column from your date table on the axis and the product name from the new table as the legend