cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Prasad1
Helper I
Helper I

Splitting stock at Product aggregate level as per the ratio of sales at Product level

Hi

 

I am rather new to PowerBI (not new to Business intelligence though. From SAP BI background).

 

I have to split stocks at Product group level to Product level based on the ratios derived from % of sales at the product level in other table. So I have following information in excel sheets

 

1. Product group - Product relation table (1:Many => 1 product group (PG) will have multiple products

2. Sales Volumes at Product level => PG Group, Product, Sales volume

3. Stock volumes at Product group level => PG Group, Stock Volume.

 

Step1: To arrive at the % of sales share of each Product in the Product group. Each PG group total sale is considered as 100% and each SKU sale with in the PG is compared with it's PG group sale total and % sales share at each Product level obtained.

 

Step 2: Take the % of sales share of each Product to the stock table and split respective Product Group stock across it's Product based on % of share.

 

Challenges:

1. Some Product groups have 2 products in it and some Product groups have more than 6 products in it.

2. Not all Products with in the product group have sales.

3. Not all Product groups have stocks. There could be zero stock for some product groups but here are sales. (Easy: No need to split zero stock :-))

 

I am struggling to achieve the above through Power BI. I tried Power Query as well but no luck yet.

 

Any guidance to resolve this ? Your help is grately appreciated.

 

I am attahcing screen shots of my tables here.

 

Product group stocks.PNGProduct gruop, Product sales volumesProduct gruop, Product sales volumes

 

 Thanks And regards

Prasad

1 ACCEPTED SOLUTION

Hey @Prasad1

 

based on your sample file I created a pbix file, this file contains three calculated columns in the sales table

 

 

Stock = 
RELATED(Stocks[Stock])

RELATED can be used, because there is one (Product Group in Stock table) to many relationship (Product Group in Sales table).

 

 

Product Share = 
DIVIDE(
    'Sales'[Sales Volume]
    ,CALCULATE(
        SUM(Sales[Sales Volume])
        ,ALLEXCEPT('Sales',Sales[Product Group])
    )
    ,BLANK()
)

and finally the product from both columns

Distributed Product Stock = Sales[Stock] * 'Sales'[Product Share] 

 

This is the result

2017-09-21_22-40-34.png 

 

Hope this is what you are looking for

 

And here is the DAX statement to create a measure in the SALES table, that calculates the "Distributed Product Stock" in one go, basically my recommendation is: go with the measure, because it has the smaller memory footprint (in comparison with calculated columns, no matter if the column was created using DAX or Power Query):

Measure = 
SUMX(
	'Sales'
	,DIVIDE(
		'Sales'[Sales Volume]
		,CALCULATE(
			SUM(Sales[Sales Volume])
			,ALLEXCEPT('Sales',Sales[Product Group])
		)
		,BLANK()
	)
	*RELATED(Stocks[Stock])
)

But sometimes Measures are not that easy to compose 😉

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

16 REPLIES 16
TomMartens
Super User II
Super User II

Hey,

 

can you please share a link to your Excel table on onedrive or dropbox, but nevertheless ...

 

As far as I understand what's not possible is to bring data from you sales table to your stock table, due to the fact there is more detail that can not be "condensed" without loosing detail.

 

If there is a relationship between your STOCK and your SALES table you can use

RELATED('stock'[Stock])

to pull data into your SALES table into a new column

 

You can use this DAX statement to create another calculated column in your SALES table that calculates the share of each product within a productgroup

share of product = 
DIVIDE(
  'sales'[Sales Volume]
  ,CALCULATE(
    SUM('sales'[Sales Volume])
    ,ALLEXECPT('sales', 'sales'[Product Group (PG)]
  )
  ,BLANK()
)
    

 Finally you can combine both columns to distribute your stockvalue

 

Hope this helps

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi

 

Thank for quick reply. I tried suggested approach but it gives an error for share of product Measure creation with DAX.

 

share of product =
DIVIDE(
  'sales'[Sales Volume ]
 ,CALCULATE(
    SUM('Sales'[Sales Volume ])
    ,ALLEXCEPT('sales', 'Sales'[Produt Group (PG) ]
  )
  ,BLANK()
))

 

It says 'A single value for column 'Sales Volume ' in table 'sales' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.'

 

Thanks

Prasad

I'm a little hasty, for this reason, forget my previous post seconds ago

 

the DAX statement from my initial post  for "share of product" will work if you create a calculated column in your sales table and not a measure.

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi Tom

 

Thanks for the quick replies. I tried the first solution as 'Calculated column' instead of Meaure. But it is still giving as issue as showed below.

 

I am trynig second solution for Measure and post the status in a short while. Hope it works.

 

Once again, thanks for your great help.

Error 2.PNG

Hi

 

Tried the second suggestion as a Measure but it is giving followig error.

 

The error is -> The True/False expression does not specify a column. Each True/False expression used as a table filter expression must refer to exactly one column

 

Thanks

Prasad

Error 3.PNG

Hi Tom

 

Tired to correct the column name as suggested for the calculated column  (not sure why it is wrong, but anyway, instead of typing, chosen from system suggestion). It is still giving error as follows.

 

The error is

 

The true/False expression does not specify a column. Each True/False expression used as a table filter expression must refer to exactly one column.

 

Thanks

PrasadError 4.PNG

@Prasad1,

 

BLANK() is the alternateresult of DIVIDE Function, note the position of the closing parenthesis for CALCULATE Function.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Please share a link to a PBIX or an Excel file, that points to OneDrive or Dropbox.

Make sure that the Sheetname matches your tablename

 

Regards

Tom 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi Tom

 

For the sake of simplicity, I kept simple Stock qty's for Product group and Sales for Products and prepared an Excel sheet as shared in my Google drive.

 

https://drive.google.com/drive/folders/0B7RkTJlMeerqOUs3R1ktYl9GV1U?usp=sharing

 

1st and 2nd tabs shows stocks and Sales

 

3rd tab shows => Step 1: Getting % share of the sale for each SKU

 

4th tab shows how it should finally come out. I did the split manually using Excel but this is to be automated, as I can't manually do it for 20,000 + rows for 14 countries.

 

Any help is greately appreciated.

 

Thanks for your help in advance.

 

Thanks

Prasad

MarcelBeug
Community Champion
Community Champion

If "Any help is greatly appreciated" why don't you react to my Power Query solution in the 3rd post of this topic?

It is working fine after adjusting the column names (Produt... --> Product..., remove trailing spaces) and the rounding to 2 decimal places.

 

Splitting stock at Product aggregate level as per the ratio of sales at Product level.png

Specializing in Power Query Formula Language (M)

Hi MarcelBeug

 

Thank you for your reply. I tried the suggested solutin earlier and it didn't work as expected. My understanding of Power Query is quite limited. So I brushed up my knowledge by doing a bit of reading. Trying your suggested solution and will respond. Once again, thank you very much for your great help

 

Regards

Prasad1

Hey @Prasad1

 

based on your sample file I created a pbix file, this file contains three calculated columns in the sales table

 

 

Stock = 
RELATED(Stocks[Stock])

RELATED can be used, because there is one (Product Group in Stock table) to many relationship (Product Group in Sales table).

 

 

Product Share = 
DIVIDE(
    'Sales'[Sales Volume]
    ,CALCULATE(
        SUM(Sales[Sales Volume])
        ,ALLEXCEPT('Sales',Sales[Product Group])
    )
    ,BLANK()
)

and finally the product from both columns

Distributed Product Stock = Sales[Stock] * 'Sales'[Product Share] 

 

This is the result

2017-09-21_22-40-34.png 

 

Hope this is what you are looking for

 

And here is the DAX statement to create a measure in the SALES table, that calculates the "Distributed Product Stock" in one go, basically my recommendation is: go with the measure, because it has the smaller memory footprint (in comparison with calculated columns, no matter if the column was created using DAX or Power Query):

Measure = 
SUMX(
	'Sales'
	,DIVIDE(
		'Sales'[Sales Volume]
		,CALCULATE(
			SUM(Sales[Sales Volume])
			,ALLEXCEPT('Sales',Sales[Product Group])
		)
		,BLANK()
	)
	*RELATED(Stocks[Stock])
)

But sometimes Measures are not that easy to compose 😉

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Brilliant Solution Tom

 

Yes. It works. I just tried it on 28,359 rows and it worked beautifully. Thank you very much for your great help. Really appreciate it.

 

Regards

Prasad

Hey,

 

I guess the table that contains the column "Sales Volume" is not called "sales".

 

Please adjust the tablename between the single qoutes accordingly, use the name of table.

 

Regards



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Sorry,

 

missed something, here is the DAX statement adjusted:

share of product =
DIVIDE(
  SUM('sales'[Sales Volume ])
 ,CALCULATE(
    SUM('Sales'[Sales Volume ])
    ,ALLEXCEPT('sales', 'Sales'[Produt Group (PG) ]
  )
  ,BLANK()
))


Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

In the meantime I came to the following Power Query solution:

 

let
    Source = Sales,
    #"Grouped Rows" = Table.Group(Source, {"Product Group (PG)"}, {{"Total Sales", each List.Sum([Sales Volume]), type number}, {"AllData", each _, type table}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Product Code", "Sales Volume"}, {"Product Code", "Sales Volume"}),
    #"Inserted Division" = Table.AddColumn(#"Expanded AllData", "Sales Percentage", each [Sales Volume] / [Total Sales], Percentage.Type),
    #"Merged Queries" = Table.NestedJoin(#"Inserted Division",{"Product Group (PG)"},Stock,{"Product Group"},"Stock",JoinKind.LeftOuter),
    #"Expanded Stock" = Table.ExpandTableColumn(#"Merged Queries", "Stock", {"Stock"}, {"Stock (PG)"}),
    AddedStockByProduct = Table.AddColumn(#"Expanded Stock", "Stock (Product)", each Number.Round([Sales Percentage] * [#"Stock (PG)"])),
    #"Removed Columns" = Table.RemoveColumns(AddedStockByProduct,{"Total Sales", "Sales Volume", "Sales Percentage", "Stock (PG)"})
in
    #"Removed Columns"
Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors