Forum Discussion
Need Help with Calculated Measure
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
Hi mistretta,
I'm trying to understand your requirement but also a little confused. You mean "I had only entered in one VENDOR and and their REQUESTED ITEM/WEIGHT.", which table do you talk about? Table A or Table B or both? And you said " I went on to add additional VENDORS.", how did you add them? etc...
I need to get more information from you so that I can understand the requirement clearly. You can post a image about the current table data and desired results, it would be better for us to test it.
Best Regards,
Qiuyun Yu
v-qiuyu-msft Sure, 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 consistent though.
I want to figure out the exact cost of each meal. To do that, I need a formula that uses my COST PER OUNCE field from Table A to multiply by the REQUIRED OUNCES from TABLE B. See screenshots below. I have 3 tables to do this.
TABLE A: Items I purchase from the grocery store. I have the DATE I went to the store, the ITEMS I purchased, the weight (now in OUNCES to be a little more realistic to this new example), and the TOTAL COST I paid for those items. Using a simple divide formula, we also have COST PER OUNCE.
TABLE B: These are the listed meals and their ingredients. So, as my columns, I have each MEAL listed, every ITEM / INGREDIENT for each meal, and REQUIRED OUNCES for each ITEM / INGREDIENT.
TABLE C: Because different MEALS can include the same ITEM / INGREDIENT (i.e. Chicken) and because I can purchase the same ITEM from multiple grocery STORES or even on separate DATES, neither TABLE A or TABLE B will have unique values for ITEM. Therefore, we now have TABLE C to have unqiue values for ITEM so that TABLE A and TABLE B can be joined together.
Link to my Excel File for Meal Example:
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 Yuv-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
Hi mistretta,
When I check the "Chicken Pot Pie" for the meal, total ounces of Chicken is 24. Please double check the attached .PBIX file.
Best Regards,
Qiuyun Yu