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.
Thanks And regards
Prasad
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
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
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
