Need Help with Calculated Measure

[mistretta]

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 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:

https://we.tl/UiZFJ5hYWp

 

------------------------------------------------------------------------------

ORIGINAL POST (RESOLVED)

I am currently using a calculated measure to multiply "COST PER LB" from Table A by "REQUESTED LBS" from Table B.

This works great when I put the calculated measure result in a bar chart, with the axis being "ITEM." On an individual basis, like in the bar chart or table, it calculates as intended.

However, I'm not able to see an accurate total. 

I'll use an example that everyone might be able to better relate to (see attached):

I go to the Grocery store to purchase 3 lbs of Bananas for $1.20. So on Table A, I have BANANAS (ITEM COLUMN), $1.20 (COST COLUMN), 3 (LB COLUMN), and $0.40 (COST PER LB COLUMN). On Table B, I have BANANAS (REQUESTED ITEM COLUMN) and 10 (REQUESTED LBS COLUMN). Using the calculated field mentioned above, multiplying "COST PER LB" from Table A by "REQUESTED LBS" from Table B, we get $4.00. So far everything is correct.

But these tables have much more than one row obviously and once you now factor in, say ORANGES (ITEM COLUMN) at $1.05 (COST PER LB COLUMN). Then again on Table B, I have ORANGES (REQUESTED ITEM COLUMN) and 20 (REQUESTED LBS COLUMN). What should be a combined total of $25.00, but inaccurately displays $43.50.

I understand the reason, which is why I underlined should and inaccuractely. The calculcation is summing the total COST PER LB to be $1.45 and multiplying that by the total REQUESTED LBS at 30 (as opposed to handling both operatings in parenethesis before summing together).

Any ideas on how would I solve this?

[v-qiuyu-msft]

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

[mistretta]

@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.

[v-qiuyu-msft]

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

[mistretta]

@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:

https://we.tl/UiZFJ5hYWp

[v-qiuyu-msft · Accepted solution]

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

[mistretta]

@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

[v-qiuyu-msft]

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