Converted products

[wailey1]

Hi

Ive hit a block.  I have built a Powerapp for stock ordering and stock counting.  This feeds into a Power Bi dashboard which combines with the sales data from EPOS to give us an idea of usage and sales to stock.

This is for drinks, so we sell in pints/half pints/measures of spirits, but we count in bottles/kegs or part bottles/kegs for example.

I have a converson table like this

ive got a sales data table that i have merged with the conversion table to get the converted sales recorded against the EPOS - (till) product.

I have an appended table that gives me the converted sales data for all products.  This also has the information of quantities ordered and the stock count at the end of the day.

This feeds to a matrix visual that - using some measures- sets the opening stock (from the previous days closing), and works out the usage.

The 'Product' column is the combination of naming convention for the cellar product (what we count in) and the till product (what we sell in).

what i need is a column that shows all of the converted sales items as a cellar product, in this example, all of the versions of 'Pimms' - Pimms Jug, Pimms to Go - to be recorded as 'Pimms' only so the converted sales for Pimms is 3.69 (kegs).

Ive struggled for a while to get to this point, so it maybe that the calculation is done somewhere else.

Thanks for any help.

[wailey1]

 Any ideas anyone??

[v-kongfanf-msft]

Hi @wailey1 ,

Can you share more details about the conversion table and sales data table structure?

Best Regards,
Adamk Kong

[wailey1]

hi

the conversion table is literally the product name as it goes through the till, what the product is called in the cellar stock and what the till product converts to as part of the cellar product.  For the below-  Till Product in the left hand column (as labelled in original post), Cellar product in the middle, conversion rate as the number.  So looking at the below, a measure of rum is 0.035.... of a bottle, Guinness Pint is 0.0056... of a keg

Therefore if i sell 10 pints of guinness, it wll equate to 0.056... of a keg in the cellar stock.

All these calculations have been made previously and come from a CSV doc.

The sales data - at present this is just a made up table, but will ultimately feed from an API connection with the tills -there are around 250 tills so this is just a very small test.  All i need from the data, however, is as below.  At present this is a CSV doc.

Let me know if you need anything else, and thanks for replying.