Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
hi
how to create below calculation in Power in a new column.
Orderd qty =SUMIF($A$2:A2,A2,$B$2:B47) |
SKU is column A , BACK QTY is column B and ordered qty is the result qty as per above formula in excel.
SKU # | BACK QTY | Ordered qty |
IX110100670 | 1000 | 1000 |
IX110100779 | 1000 | 1000 |
IX110100670 | 1000 | 2000 |
IX110100779 | 1000 | 2000 |
IX110100670 | 1000 | 3000 |
IX110100670 | 1000 | 4000 |
IX110100670 | 1000 | 5000 |
IX110100670 | 1000 | 6000 |
IX110100670 | 3000 | 9000 |
IX110100779 | 20000 | 22000 |
Solved! Go to Solution.
Hi @sunil3M ,
Try the following formual:
Order qty1 =
SUMX (
FILTER (
'Table',
[SKU #] = EARLIER ( [SKU #] )
&& [Index] <= EARLIER ( [Index] )
),
[BACK QTY]
)
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @sunil3M ,
Try the following formual:
Order qty1 =
SUMX (
FILTER (
'Table',
[SKU #] = EARLIER ( [SKU #] )
&& [Index] <= EARLIER ( [Index] )
),
[BACK QTY]
)
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@sunil3M , You need have date column. If not add an index column and try
Add an index column https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi
New column = sumx(filter(Table, [SKU #] =earlier([SKU #]) && [Index] <= earlier([Index])),[BACK QTY])
or new measure =
sumx(filter(allselected(Table), Table[SKU #] =max(Table[SKU #]) && Table[Index] <= max(Table[Index])),Table[BACK QTY])
User | Count |
---|---|
139 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |