Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Good evening please i need your help I wrote this code to calculate the final stock; the problem is that I want to apply a loop on 'code article' and 'division'. each article in one of its two divisions has its own stock
the stock of the article in the 2 divisions is independent
'dataset' holds the input data for this script
import pandas as pd
DX= pd.DataFrame(dataset.loc[:,])
AAA = []
for i in range(len(DX)):
if(i==0):
var = DX['Stock 10 fixe'][0] + DX['Livraison'][i]-DX['consomation'][i]
AAA.append(var)
else:
var = var + DX['Livraison'][i]-DX['consomation'][i]
AAA.append(var)DX['stock Final']=AAA
the result of my code is false (only good for M100 and DPC1 i want that ( every 'code article' in every 'Division' has his own stack of the last 4 months
Solved! Go to Solution.
Hi @Anonymous ,
I'm not sure if it's possible to achieve the result you want with phython, but it can be achieved with DAX... I create a sample pbix file(see attachment), please check whether it can work as a workaround.
1. Add Index column group by Code Aritcle and Division according to month (as shown below) in Power Query Editor
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZA9D4MgEIb/C7O5wHEKznUi6cduHKph6NCaGOPvL2iaGBAaWQ4uD8fD27bsKjhnBWseF+EKluvpPsxjbye3EwJIucpZV4Sw397Gxb77yfq7Gmq9zUjAjR1+sCSo1gd5CjbPz/LaHCoNvkvhYOO7UobKBLqMlc2BskQgryxDC3OgrDjo2isTJeCdstKAXkKU9Qbj/n/qT8x4JuYIzsUcwZmYcf+/yq1czHgm5gjOxRzBqZi7Lw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Code Article" = _t, Division = _t, #"Stock 10 fixe" = _t, Month = _t, consomation = _t, Livraison = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Code Article", type text}, {"Division", type text}, {"Stock 10 fixe", Int64.Type}, {"Month", type text}, {"consomation", type number}, {"Livraison", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Code Article", "Division"}, {{"Index", each Table.AddIndexColumn(_, "Index",1,1), type table [Code Article=nullable text, Division=nullable text, Stock 10 fixe=nullable number, Month=nullable text, consomation=nullable number, Livraison=nullable number, Index=nullable number]}}),
#"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"Stock 10 fixe", "Month", "consomation", "Livraison", "Index"}, {"Stock 10 fixe", "Month", "consomation", "Livraison", "Index"})
in
#"Expanded Index"
2. Create a measure or calculated column as below to get the final stock
Measure:
stock Final =
VAR _selcodea =
SELECTEDVALUE ( 'Table'[Code Article] )
VAR _seldiv =
SELECTEDVALUE ( 'Table'[Division] )
RETURN
CALCULATE (
SUM ( 'Table'[Stock 10 fixe] ) + SUM ( 'Table'[Livraison] )
- SUM ( 'Table'[consomation] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Code Article] = _selcodea
&& 'Table'[Division] = _seldiv
&& 'Table'[Index] <= SELECTEDVALUE ( 'Table'[Index] )
)
)
Or Calculated column
Column_stockFinal =
CALCULATE (
SUM ( 'Table'[Stock 10 fixe] ) + SUM ( 'Table'[Livraison] )
- SUM ( 'Table'[consomation] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Code Article] = EARLIER('Table'[Code Article])
&& 'Table'[Division] = EARLIER('Table'[Division])
&& 'Table'[Index] <= EARLIER ( 'Table'[Index] )
)
)
Best Regards
Hi @Anonymous ,
I'm not sure if it's possible to achieve the result you want with phython, but it can be achieved with DAX... I create a sample pbix file(see attachment), please check whether it can work as a workaround.
1. Add Index column group by Code Aritcle and Division according to month (as shown below) in Power Query Editor
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZA9D4MgEIb/C7O5wHEKznUi6cduHKph6NCaGOPvL2iaGBAaWQ4uD8fD27bsKjhnBWseF+EKluvpPsxjbye3EwJIucpZV4Sw397Gxb77yfq7Gmq9zUjAjR1+sCSo1gd5CjbPz/LaHCoNvkvhYOO7UobKBLqMlc2BskQgryxDC3OgrDjo2isTJeCdstKAXkKU9Qbj/n/qT8x4JuYIzsUcwZmYcf+/yq1czHgm5gjOxRzBqZi7Lw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Code Article" = _t, Division = _t, #"Stock 10 fixe" = _t, Month = _t, consomation = _t, Livraison = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Code Article", type text}, {"Division", type text}, {"Stock 10 fixe", Int64.Type}, {"Month", type text}, {"consomation", type number}, {"Livraison", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Code Article", "Division"}, {{"Index", each Table.AddIndexColumn(_, "Index",1,1), type table [Code Article=nullable text, Division=nullable text, Stock 10 fixe=nullable number, Month=nullable text, consomation=nullable number, Livraison=nullable number, Index=nullable number]}}),
#"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"Stock 10 fixe", "Month", "consomation", "Livraison", "Index"}, {"Stock 10 fixe", "Month", "consomation", "Livraison", "Index"})
in
#"Expanded Index"
2. Create a measure or calculated column as below to get the final stock
Measure:
stock Final =
VAR _selcodea =
SELECTEDVALUE ( 'Table'[Code Article] )
VAR _seldiv =
SELECTEDVALUE ( 'Table'[Division] )
RETURN
CALCULATE (
SUM ( 'Table'[Stock 10 fixe] ) + SUM ( 'Table'[Livraison] )
- SUM ( 'Table'[consomation] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Code Article] = _selcodea
&& 'Table'[Division] = _seldiv
&& 'Table'[Index] <= SELECTEDVALUE ( 'Table'[Index] )
)
)
Or Calculated column
Column_stockFinal =
CALCULATE (
SUM ( 'Table'[Stock 10 fixe] ) + SUM ( 'Table'[Livraison] )
- SUM ( 'Table'[consomation] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Code Article] = EARLIER('Table'[Code Article])
&& 'Table'[Division] = EARLIER('Table'[Division])
&& 'Table'[Index] <= EARLIER ( 'Table'[Index] )
)
)
Best Regards
User | Count |
---|---|
5 | |
4 | |
3 | |
2 | |
2 |
User | Count |
---|---|
8 | |
6 | |
4 | |
4 | |
4 |