Sales Filtering by a Measure

[Anonymous]

Hello Everyone, 

Firstable, thank you in advance for help. I will to be the more clear as I can.

Here the context. I need to calculate the sales per group of stores which are rattached to a warehouse. To do this, I have several warehouses and each stores is rattached to only one of them. 

To make it simple, let say, we have 2 warehouses (W_A and W_B) and 8 (Store 1, Store 2.... Store 😎 stores. And in my world:

• Each warehouse order its products to the suppliers. 
  
• Then, the stores order the product to the warehouse.
• The point is that the warehouse order its quantity depending on the sales of stores which are rattached to them. 
• That's why i need to split the sales between the warehouse

Today I can identify which stores is rattached to which warehouse and I have all sales for all stores per products, thanks to a measure. 

• This measure let me identified the stores rattached the warehouse : 

CONCATENATEX(VALUES(Data_Order[No Store]);Data_Order[No Store];", ")

Below, you can find how is build my sales table:

To resume in one sentence :  I want to know the sales of the stores that are rattached to the warehouse. Below and a last example :

• My stores 1, 2 and 3 are rattached to the warehouse A. And, therefore ordering to the warehouse A. 
• Also, note that I'm finding which Store order to which warehouse thanks to a measure.
• I need to know the sales of the stores 1, 2 and 3 in order to know how much the warehouse have to order to the supplier. 
  

And the measure I already tried (The first part is for calculate the monthly sales:

   CALCULATE(AVERAGEX(VALUES(LookUp_Calendar[YYYYMM]);[Quantity Sold]);FILTER(Data_Sales;VALUES(Data_Sales[No Stores]))))

Thank you and have a great day

Erwan

[lc_finance]

Hi @Anonymous ,

can you share a sample Power BI file via DropBox, One Drive, Drive or another similar tool?

That will make it easier to help you.

Regards,

LC

[Anonymous]

Hi LC, 

I did not think it was going to be so complicated haha

Here the link : Link PBI

Thanks again

[lc_finance]

Hi @Anonymous ,

I checked it. When I use the column BU from the Sales table instead of the Order table, the matrix works well.

I see that the formula for the BU name is :

BU Name order = CONCATENATEX(
		    VALUES('Order'[BU]),'Order'[BU],", ")

If you want to use it to split sales (and not orders) by BU, why not changing it to:

BU Name sales = CONCATENATEX(
		    VALUES('Sales'[BU]),'Sales'[BU],", ")

Let me know if it works,

LC

www.finance-bi.com

[Anonymous]

Hi @lc_finance 

Thanks for answering. I attached the PBI

In my example, I have the total quantity sold equal to 1200 pieces for 1 product. But I need the quantity sold by BU. 

I need to see 500 pieces for Europe and 700 pieces for America. We have to take into account that my data are built exactly as the PBI I attached. therefore, I can't add a product type in my sales table.  (Which is in reality a carton bar code). 

I hope this time, it will be more clear. 

Thanks in advance,

Here the document : Link to PBI

Erwan

[lc_finance]

Hi @Anonymous ,

it looks like the link to the Power BI file is not a public link so I cannot access it. 
You could check if it's possible to make it public?

Regards

LC