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,
i want to find out a DAX formula to get below calculated column (that is the result column that i want)
logic is as below
there are rows with different Code numbers and one code is considerd as one block (that means calculations should apply for each code separetly)
1. find out the first greater than 0 Stock value for each code
2. copy that value and 0 values (which are in above rows) to calculated column
3. all other values below to that value(1) should equal to value in Quantity column
i have showed the expected output in 'calculated' column.
Highly appricate any input
thank you
Solved! Go to Solution.
Hi @Anonymous
Create two columns
min_rank = RANKX(FILTER(Sheet2,Sheet2[code]=EARLIER(Sheet2[code])),[stock],,ASC,Dense) Column = VAR value_rank1 = CALCULATE ( SUM ( [stock] ), FILTER ( ALLEXCEPT ( Sheet2, Sheet2[code] ), Sheet2[min_rank] = 1 ) ) RETURN IF ( value_rank1 = 0, IF ( [min_rank] = 1 || [min_rank] = 2, [stock], [quantity] ), IF ( [min_rank] = 1, [stock], [quantity] ) )
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Create two columns
min_rank = RANKX(FILTER(Sheet2,Sheet2[code]=EARLIER(Sheet2[code])),[stock],,ASC,Dense) Column = VAR value_rank1 = CALCULATE ( SUM ( [stock] ), FILTER ( ALLEXCEPT ( Sheet2, Sheet2[code] ), Sheet2[min_rank] = 1 ) ) RETURN IF ( value_rank1 = 0, IF ( [min_rank] = 1 || [min_rank] = 2, [stock], [quantity] ), IF ( [min_rank] = 1, [stock], [quantity] ) )
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |