Forum Discussion
Need to Swap values in columns
We have Unit price with us for various items
Need to calculate actual and weightage by dax
Actual - divide (unit price/total unit price)
Weightage is flipped- lowest of actual is assigned to highest unit price. Second lowest actual to second highest unit price and so on..
Weightage is the Final output needed
2 Replies
Calculate Actual as the ratio of Unit Price to the total Unit Price
Actual =
DIVIDE(
Items[Unit Price],
CALCULATE(SUM(Items[Unit Price]), ALL(Items))
)Rank Unit Prices in descending order
UnitPriceRank =
RANKX(
ALL(Items),
Items[Unit Price],
,
DESC,
DENSE
)Rank Actual values in ascending order
ActualRank =
RANKX(
ALL(Items),
Items[Actual],
,
ASC,
DENSE
)Assign Weightage by matching the ranks of UnitPriceRank and ActualRank
Weightage =
CALCULATE(
MAXX(
FILTER(
ALL(Items),
Items[ActualRank] = Items[UnitPriceRank]
),
Items[Actual]
)
) Let me know if it helped you a bit and if you need further assistance or have any questions!
