Forum Discussion

Frequent Visitor

10 years ago

Solved

Need Help with Calculated Measure

EDIT: Let's just start from the beginning with a new example rather than trying to throw in Vendors to the mix of my original fruit example. We will stick with food and weight to keep it consiste...

Need Help

v-qiuyu-msft
10 years ago
Hi mistretta,

In your scenario, you can built a relationship between Table A and Table B as 1:N, then create a measure in Table B like below:

Total = SUMX('Table B',CALCULATE(SUM('Table B'[REQUIRED OUNCES]))*RELATED('Table A'[COST PER OUNCE]))

Of you can create a table from the Table B, build a relationship between Table A and new table as 1:1, then create a measure. See:

Table = SUMMARIZE('Table B','Table B'[ITEM / INGREDIENT],"Total OUNCES", SUM('Table B'[REQUIRED OUNCES]))

TotalCost = SUMX('Table A','Table A'[COST PER OUNCE]*RELATED('Table'[Total OUNCES]))

Best Regards,
Qiuyun Yu
test0811.pbix411 KB

mistretta

Frequent Visitor

10 years ago

v-qiuyu-msft, thanks. I was able to use the TABLE and TOTALCOST method to replicate what you did. We're just about there with only one issue I see left.

See two screenshots attached. In the screenshot captioned "Unfiltered", you will see on TABLE B that Chicken is used in both meals. 24 ounces of Chicken is required for Chicken Pot Pie and 16 ounces of Chicken is required for Chicken Quesadilla's (for a sum-total of 40 ounces). When unfiltered, you get an accurate result.

However, if I filter the new output table for a particular meal (see screenshot captioned "Filtered"), then it still shows I need a sum-total of 40 ounces of Chicken, instead of the 24 ounces that the meal requires.

FilteredUnfiltered