Forum Discussion
Helper IISales and Stock Data Value mismatch issue
Hi all,
I’m struggling with a Power BI issue and would appreciate guidance. Here’s the scenario:
I have two fact tables: fact_sales (5 years of sales) and fact_stock_monthend (latest stock data only).
I created a dimension table Dim_Location_SS2 via DISTINCT(UNION(...)) combining locations from both fact tables.
Country-wise visuals work perfectly.
Location-wise visuals exclude UE1 (one store), even though it exists in all tables.
Observations / Tests I’ve done:
UE1 exists in all tables (fact_sales, fact_stock_monthend, Dim_Location_SS2).
Relationships are one-to-many, single direction, no errors.
HERE this is my sample table you can see the value mismatch in country table and location table
Here Below let me add more details:
Location dim for stock and sale:this includes all locationsDim_Location_SS2 =DISTINCT (UNION (SELECTCOLUMNS (fact_sales,"Country", UPPER(TRIM(fact_sales[Country])),"LocationCode", UPPER(TRIM(fact_sales[Location_Code]))),SELECTCOLUMNS (fact_stock_monthend,"Country", UPPER(TRIM(fact_stock_monthend[Country])),"LocationCode", UPPER(TRIM(fact_stock_monthend[Location])))))
measures:Stock Qty =CALCULATE(SUM(fact_stock_monthend[Qty]),fact_stock_monthend[Bin] <> "TED_SALE",fact_stock_monthend[Bin] <> "MEL")Sales Qty (Filtered) =CALCULATE(SUM(fact_sales[Quantity]))Stock Wt =CALCULATE(SUM(fact_stock_monthend[Gross_Weight]),fact_stock_monthend[Bin] <> "TED_SALE",fact_stock_monthend[Bin] <> "MEL")Sales Wt (Filtered) =CALCULATE(SUM(fact_sales[Gross_Weight]))Total Stock Value in Cr FSM (Filtered) =DIVIDE(CALCULATE(SUMX(fact_stock_monthend,COALESCE(fact_stock_monthend[UCP_QAR_INR],fact_stock_monthend[UCP_OMR_INR] +fact_stock_monthend[UCP_SGD_INR] +fact_stock_monthend[UCP_USD_INR] +fact_stock_monthend[UCP_AED_INR])),NOT fact_stock_monthend[Bin] IN {"TED_SALE", "MEL"}),10000000)Total Sales Value in Cr stock =SUMX('fact_sales',IF(NOT(ISBLANK('fact_sales'[UCP_QAR_INR])),'fact_sales'[UCP_QAR_INR],'fact_sales'[UCP_OMR_INR] +'fact_sales'[UCP_SGD_INR] +'fact_sales'[UCP_USD_INR] +'fact_sales'[UCP_AED_INR])) / 10000000
The key point to look for is in locationcode few location code is not included thats the case which causes mismatch
v-venuppu Yeah This is solved. Visual Level filter logic is the culprit an dit is resolved.
7 Replies
- DataNinja777
Super User
Hi GanesaMoorthyGM ,
If you look at the totals in the visual, the Country view shows 1,716, but the Location view drops to 1,599.
Since the data is clearly there (in the Country view) but disappears when you slice by Location, it confirms the relationship is breaking on those specific codes. It's due to a 'ghost character' or whitespace mismatch.
I suspect this is a hidden character issue that dax trim isn't catching (like a non-breaking space).
In order to fix the issue:
- Check the string length using LEN() on the 'UE1' code in both tables to see if they match.
- Move the cleaning to Power Query using Text.Clean and Text.Trim to strip out any control characters before the data loads.
Best regards,
GanesaMoorthyGMHi Guys I found the issue, it's because the filtering logic that i used in visual lvl filter in both country wise and stock wise i used Stock Qty is not blank so that it excludes all the values where stock data is not present. So for that particula location 'UE1' there is no stock data but has sales data. Though Stock qty is 0 for UE1 it is neglected. So now i need to write dynamic measure. And i need your help.
v-venuppuCommunity Support
Hi GanesaMoorthyGM ,
Thank you for reaching out to Microsoft Fabric Community.
Thank you DataNinja777 for the prompt response.
UE1 was missing because the visual had a filter Stock Qty is not blank, so any location with Sales but no Stock (like UE1) was excluded.
To fix this, create a visibility measure that shows a location if it has either Stock or Sales:
Show Location =
VAR _Stock = [Stock Qty]
VAR _Sales = [Sales Qty (Filtered)]
RETURN IF(NOT(ISBLANK(_Stock)) || NOT(ISBLANK(_Sales)), 1, 0)Then, in the visual filters, use Show Location = 1 instead of filtering on Stock Qty.
This ensures UE1 and any similar stores are included, and country-level and location-level totals match correctly.
- v-venuppu
Hi GanesaMoorthyGM ,
I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.
Thank you.
- v-venuppu
Hi GanesaMoorthyGM ,
May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.
Thank you.
- GanesaMoorthyGM
v-venuppu Yeah This is solved. Visual Level filter logic is the culprit an dit is resolved.
- v-venuppu
Hi GanesaMoorthyGM ,
Thank you for confirming that the issue got resolved.If any of the responses guided you in solving the issue, I would suggest to accept that response as a solution,so other members can easily find it.
