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,
In your scenario, you can create a calculated column in Table A like below:
Total = 'Table A'[COST PER LB] * RELATED('Table B'[REQUESTED LBS])
Then place this value in a table visual, set this field "SUM".
Or you can create a measure in Table A like below:
Total Cost = SUMX('Table A','Table A'[COST PER LB] * RELATED('Table B'[REQUESTED LBS]))
If you have any question, please feel free to ask.
Best Regards,
Qiuyun Yu
v-qiuyu-msft Thank you so much. This worked perfectly!! I really appreciate the detailed response and you taking the time to help.
EDIT: v-qiuyu-msft Actually I do need help once more. Your solution worked perfectly for what I had explained and originally setup. But it just got more complicated once I threw another curveball to the mix.
So I didn't get too far into my project before I realized the first issue that I posted about. I had only entered in one VENDOR and and their REQUESTED ITEM/WEIGHT.
After your solution, I went on to add additional VENDORS. Well some Vendors request the same items. So at this point, I can no longer relate TABLE A and TABLE B by the ITEM COLUMN. Now I need to bring in a new TABLE C into the mix to house unique values for ITEM so that I can relate TABLE A and TABLE B together again.
However, it seems that I can no longer use either previous solution (more specifically, the "RELATED" syntax) since TABLE A and TABLE B are no longer directly joined together.
Any new ideas? I really appreciate the help.
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 Yuv-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 Yu