Forum Discussion
Sales Price for Generation Product
Hi,
I have following basis for calculation:
Product X has 2 generation: 1 and 2
I have a historical table of the revenues and quantitiy per month for the products
Now what i need is a line chart for the sales price for Product X for Generation 1 and 2 and the cost development.
Idieally it should be 2 lines --> one line showing prices for G1 and second for G2
The Quantities should be shown as staked column, so I can see the development of the prices for G1 and G2 and the total volume increase for product X.
Due to Excel issues, I splitted the datatables into:
Historical Turnover: Sales Prices, Revenue, Quantities per material number and per date (01.01.2018 format)
Historical Costs: Product Costs per pcs per material number and per date
Date Key
Generation Table: Product, G1 (if G1 its an x, otherwise blank), G2 (if G2 its an x otherwise blank), Material Number
Each generation has its own material number.
That's how I tried to calculate the old sales unit price: DIVIDE( CALCULATE([Total Net Sales];Generation[G1]="x";'Date Key'[Date]);CALCULATE([Quantity sold];Generation[G2]="x";'Date Key'[Date]))
But if I add in the chart the date from the key table, then the prices are the same for all dates. Same with the Product Costs if I add it to the chart.
If I use the date from the Historical Turnover, then the Unit Sales price is moving according the dates but the Product Costs are not changing at all, same value.
What do I do wrong?
Thank you
Ana
4 Replies
Hi,
I have following basis for calculation:
Product X has 2 generation: 1 and 2
I have a historical table of the revenues and quantitiy per month for the products
Now what i need is a line chart for the sales price for Product X for Generation 1 and 2 and the cost development.
Idieally it should be 2 lines --> one line showing prices for G1 and second for G2
The Quantities should be shown as staked column, so I can see the development of the prices for G1 and G2 and the total volume increase for product X.
Due to Excel issues, I splitted the datatables into:
Historical Turnover: Sales Prices, Revenue, Quantities per material number and per date (01.01.2018 format)
Historical Costs: Product Costs per pcs per material number and per date
Date Key
Generation Table: Product, G1 (if G1 its an x, otherwise blank), G2 (if G2 its an x otherwise blank), Material Number
Each generation has its own material number.
That's how I tried to calculate the old sales unit price: DIVIDE( CALCULATE([Total Net Sales];Generation[G1]="x";'Date Key'[Date]);CALCULATE([Quantity sold];Generation[G2]="x";'Date Key'[Date]))
But if I add in the chart the date from the key table, then the prices are the same for all dates. Same with the Product Costs if I add it to the chart.
If I use the date from the Historical Turnover, then the Unit Sales price is moving according the dates but the Product Costs are not changing at all, same value.
What do I do wrong?
Thank you
Ana
v-yuta-msftCommunity Support
Hi Anastasija,
Based on your description, you create a measure to calculate the old sales unit price in Generation table, right? So what's the relationship between Date Key table and Generation table? Measures [Total Net Sales] and [Quantity sold] are based on which columns? And what's the logic of your measures?
In addtion, if possible, can you share some data and clarify more details to test?
Regards,
Jimmy Tao
Hi,
thank you for the support. Yes, I did a measure to calculate the Unit Price but not in the generation table, I did it in the Historical Turnover table.
Attached please find the example for the product with 2 generations.
The relation is between material numbers inbetween all tables. There is one Key table for material available as well.
The Date key is linked to Date in Cost table and in Historical Turnover table. So i thought that in such a case the calculation for generation should work, as the material numbers in the generation key table are as well linked to the historical table and cost table.How can I share the Report with you? I prepared a small example and thought I could share it. Or how can I attach? I do not see any attachment options.
Regards,
Anastasija
