Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi PowerBI masters,
Here comes a challenge , which I searched a lot of place for help but in vain.
I am trying to calculate a column , where the it is based on the values of former /Above Row(s).
an example is : (How could we Calcluate the column with Red header?)
Batch Key | Key(Product+country) | Category Short Text | Receipt Quantity / Requirements Quantity | Product Number | Country | Batch # | Expire date | Sales Forecast Until Expire Date | Total Stockavailable | Accumulated Stock of F Col | Sales Forecast between Two Expire Date | Scrap Volume between 2 expire dates | Accumulated Scrap Volume |
18134 PT A15134 | 18134 PT | Stock | 29 | 18134 | PT | A15134 | 19-08-2017 | 0,00 | 8815 | 29 | 0 | 29 | 29 |
18134 PT A24327 | 18134 PT | Stock | 38 | 18134 | PT | A24327 | 21-10-2017 | 0,00 | 8815 | 67 | 0 | 38 | 67 |
18134 PT A37300 | 18134 PT | Stock | 40 | 18134 | PT | A37300 | 01-12-2017 | 0,00 | 8815 | 107 | 0 | 40 | 107 |
18134 PT A65180 | 18134 PT | Stock | 4.828 | 18134 | PT | A65180 | 21-12-2018 | -3450,00 | 8815 | 4935 | -3450 | 1378 | 1485 |
18134 PT A72984 | 18134 PT | Stock | 3.880 | 18134 | PT | A72984 | 03-03-2019 | -6014,00 | 8815 | 8815 | -2564 | 1316 | 2801 |
Attached in the link is the excel with Formuler,
https://www.dropbox.com/s/acfaojhlrv1sc83/example%20sheet.xlsm?dl=0
Could any Masters help with it?
Many Thanks in advance!
Mvh
Yuqi
Solved! Go to Solution.
hi, @Anonymous
I met a few times before this problem, It is because of the same Expire date for one Product+country has multiple Batch #.
So you could use this formula to add a rank column
Rank = RANKX ( FILTER ( Test105PrevRow, Test105PrevRow[Key(Product+country)] = EARLIER ( Test105PrevRow[Key(Product+country)] ) ), RANKX ( FILTER ( Test105PrevRow, Test105PrevRow[Key(Product+country)] = EARLIER ( Test105PrevRow[Key(Product+country)] ) ), Test105PrevRow[Expire date], , ASC ) + DIVIDE ( RANKX ( FILTER ( Test105PrevRow, Test105PrevRow[Key(Product+country)] = EARLIER ( Test105PrevRow[Key(Product+country)] ) ), Test105PrevRow[Batch #], , ASC ), ( COUNTROWS ( FILTER ( Test105PrevRow, Test105PrevRow[Key(Product+country)] = EARLIER ( Test105PrevRow[Key(Product+country)] ) ) ) + 1 ) ), , ASC )
Then use other formulas to continue to calculate
Result:
By the way, for your special case.
you could use this simple formula:
simple rank = RANKX ( FILTER ( Test105PrevRow, Test105PrevRow[Key(Product+country)] = EARLIER ( Test105PrevRow[Key(Product+country)] ) ),Test105PrevRow[Batch Key],,ASC)
Best Regards,
Lin
hi, @Anonymous
I met a few times before this problem, It is because of the same Expire date for one Product+country has multiple Batch #.
So you could use this formula to add a rank column
Rank = RANKX ( FILTER ( Test105PrevRow, Test105PrevRow[Key(Product+country)] = EARLIER ( Test105PrevRow[Key(Product+country)] ) ), RANKX ( FILTER ( Test105PrevRow, Test105PrevRow[Key(Product+country)] = EARLIER ( Test105PrevRow[Key(Product+country)] ) ), Test105PrevRow[Expire date], , ASC ) + DIVIDE ( RANKX ( FILTER ( Test105PrevRow, Test105PrevRow[Key(Product+country)] = EARLIER ( Test105PrevRow[Key(Product+country)] ) ), Test105PrevRow[Batch #], , ASC ), ( COUNTROWS ( FILTER ( Test105PrevRow, Test105PrevRow[Key(Product+country)] = EARLIER ( Test105PrevRow[Key(Product+country)] ) ) ) + 1 ) ), , ASC )
Then use other formulas to continue to calculate
Result:
By the way, for your special case.
you could use this simple formula:
simple rank = RANKX ( FILTER ( Test105PrevRow, Test105PrevRow[Key(Product+country)] = EARLIER ( Test105PrevRow[Key(Product+country)] ) ),Test105PrevRow[Batch Key],,ASC)
Best Regards,
Lin
Hej Lin
I figured out the other formulars.
however your Rank is the Key.
Many thanks for help!
Hej Lin, @v-lili6-msft
Firstly thanks for your great Help!
So far we have the right Rnk !
However the other fomulars of calculation for Scrapped volumn is not right ( or maybe I understood it wrong).
it does not solve the follwoing senarios.
1. It is now using my "Accumulated Scrap Volumn" to calculate my "Scrap Volumn between two expire dates". The Logic should be opposit.
2.BlockedStock should go Scrpped Volumn directly. ( I can handle it by seperating them into a seperate table, however hope there could be a way to handle that in a same table.
Since I am new in PowerBI(2 months),
Could you Kindly help me do a detail pbx based on the 3 examples?
https://www.dropbox.com/s/ukcwwhkcmaz19d9/Test%203%20examples.xlsx?dl=0
Many thanks in advance!
@Anonymous Please try below steps:
Step1: Create an Index field as below
Rnk = RANKX(FILTER(Test105PrevRow,Test105PrevRow[KeyProdCntry]=EARLIER(Test105PrevRow[KeyProdCntry])),Test105PrevRow[ExpireDate],Test105PrevRow[ExpireDate],ASC)
Step2: Create as a "New Column"
ScrapVolume = VAR _Curr = Test105PrevRow[Accumulated Scrap Volume] VAR _Prev = CALCULATE(MIN(Test105PrevRow[Accumulated Scrap Volume]),FILTER(ALL(Test105PrevRow),Test105PrevRow[KeyProdCntry]=EARLIER(Test105PrevRow[KeyProdCntry]) && Test105PrevRow[Rnk] = EARLIER(Test105PrevRow[Rnk])-1)) RETURN _Curr-_Prev
Step3 : Create as a "New Column"
SalesForeCastb/wExprieDates = VAR _Curr = Test105PrevRow[SalesForecast] VAR _Prev = CALCULATE(SUM(Test105PrevRow[SalesForecast]),FILTER(ALL(Test105PrevRow),Test105PrevRow[KeyProdCntry]=EARLIER(Test105PrevRow[KeyProdCntry]) && Test105PrevRow[Rnk] = EARLIER(Test105PrevRow[Rnk])-1)) RETURN _Curr-_Prev
The result will look like... (Last three columns are the new additional fields)
Proud to be a PBI Community Champion
Hej PattemManohar, @PattemManohar
Thanks for your help anyway.
Your suggestion was a quite good start and very nearby the answer!
Best regards,
Yuqi
Hej PattemManohar, @PattemManohar
Thanks for your help anyway.
Your suggestion was a quite good start and very nearby the answer!
Best regards,
Yuqi
Here is A bit mroe info using your steps:
Hej PattemManohar,
Many thanks for your fast reply!
I tried your steps , but It does not work.
If I understood it rightly, you are using my last column to calculate the Scrapped Vol.
Let me present in another way, Could you kindly help calculate all last 4 columns in my examples?
I have put the 3 examples and their detail exaplaination in the following link.
https://www.dropbox.com/s/ukcwwhkcmaz19d9/Test%203%20examples.xlsx?dl=0
Hope that you could help through.
Many thanks for your time again!
Best regards,
Yuqi
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |