PLEASE BE INFORMED THAT I WANT TO HAVE THE SOLUTION IN EXCEL NOT POWER BI
Hi all,
I have a dataset and want to perform some calculations with a measure. To this end, I have changed the AdventureWorks dataset (publicly available) to make my problem more understandable.
I have the following tables:
- Product_Price
This table lists the price of a product at a specific date. I made a unique column, which consists of the product key and the date.
- Product_Static
This table lists the details of a product key: name of the product and the color.
- FX
For each day and for each currency, the conversion rate.
- Sales
For each product (ProductKey) sold, the date and quantity. Column B makes the line unique: =C2&E2.
What I have so far in Excel is a prototype see screenshot
- in cell E3 we have the formula =UNIQUE(Sales!C2:C41,FALSE,FALSE). This populated the column with all the products that are sold
- in cell B3 I have a dropdown with the date.
When a date is selected in B3, the following happens:
- Column F: =VLOOKUP($E3,Product_static!$A:D,2,FALSE) The product name of Productkey in column E is displayed.
- Column G: =VLOOKUP($E3,Product_static!$A:E,3,FALSE) The color of Productkey in column E is displayed.
- Column H: =SUMIFS(Sales!D:D,Sales!C:C,E3,Sales!E:E,"<="&$B$3). The number of products of Productkey that are sold up until the day (and included that day) selected in cell B3.
- Column I =VLOOKUP(E3&$B$3,Product_Price!B:E,4,FALSE): the price of ProductKey for that day is selected.
- Column J =VLOOKUP(E3,Product_Price!D:F,3,FALSE): the currency for the Productkey is displayed
- Column K =I3*H3: the sales amount in the currency is calculated.
- Column L: =VLOOKUP(J3&$B$3,FX!A:D,4,FALSE)*K3: the sales in GBP is calculated
So far I have only this:
It shows only that day.
Total_Sales is a measure (and the only measure I have so far,
total_sales = SUMx(Sales,Sales[Quantity]*RELATED(ProductPrice[Price]))
Also, the FX table does not connect automatically to any of the other tables. Why is that?
Please see a link to Excel sheet: Click here
Thanks,
Naveen
