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.
product name | qty | amount | cost[amount/qty] | cost%[cost/total cost) |
a | 10 | 1000 | 100 | 33.33333 |
b | 20 | 2000 | 200 | 66.66667 |
total | 300 | 100% |
my measure is
Solved! Go to Solution.
hi, @aravindh10
Just try this formula to create a meausre
% cost per qty = var _costytable= SUMMARIZE(abc,abc[MonthYear],"_cost1",DIVIDE([amount],[qty])) var _totaltable=SUMMARIZE(ALLSELECTED(abc),abc[MonthYear],"_cost2",DIVIDE([amount],[qty])) return DIVIDE(SUMX(_costytable,[_cost1]),SUMX(_totaltable,[_cost2]))
Result:
here is pbix file, please try it.
Best Regards,
Lin
hi, @aravindh10
i want measure.Only available columns are qty and amount
Do you mean that [amount] and [qty] are column not measure
If so, your formula is wrong in measure
And what is Hybris table in the formula.
try this formula:
% cost per qty = var total_cost= DIVIDE(SUM([amount]),SUM([qty])) return CALCULATE(DIVIDE(DIVIDE(SUMX(abc,[amount]),SUMX(abc,Hybris[SOLD_QTY])),cost))
If you not your case, please share some complete sample data and expected output.
Best Regards,
Lin
field is available as Measure | field is available as Measure | what I got using the measure which i got from the community | cost | cost% | Required Measure | |
MonthYear | Amount | Qty | % cost per qty | Amount/Qty | cost/total cost | % cost per qty |
19-May | $20,379,005 | 38405 | 1.026826333 | 530 | 530/1525 | 34% |
19-Jun | $11,952,378 | 24142 | 0.958038303 | 495 | 495/1525 | 32% |
19-Apr | $4,957,268 | 9610 | 0.998207489 | 515 | 515/1525 | 33% |
1525 | 100% |
hi, @aravindh10
Just try this formula to create a meausre
% cost per qty = var _costytable= SUMMARIZE(abc,abc[MonthYear],"_cost1",DIVIDE([amount],[qty])) var _totaltable=SUMMARIZE(ALLSELECTED(abc),abc[MonthYear],"_cost2",DIVIDE([amount],[qty])) return DIVIDE(SUMX(_costytable,[_cost1]),SUMX(_totaltable,[_cost2]))
Result:
here is pbix file, please try it.
Best Regards,
Lin
You could use a calculated column like this:
% cost per qty = cost[cost[amount/qty]]]/SUM(cost[cost[amount/qty]]])
Best Regards,
Miguel
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
i want measure.Only available columns are qty and amount
Please, review your raw data, the second row is wrong and if cost would equal for two row.
And the other hand, calculated columns are the best way to get your goal.
Column cost would be:
Cost = cost[amount]/cost[qty]
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 |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |