refer the value of a measure as static in another measure

[data_team_afri]

Hi all,

I have prolem while referring a measure in other measures. When I referred to the value of 1st measure in the second measure, it does not stay static. However, I need it to be fixed for further calculation. 

The structure is like following.

Table 04 includes revenue data per date per product;

Table 08 includes sessions per date per product;

(please note that table 04 & table 08 are not directly related)

Table Date includes date;

Table 24 includes cost per date per campaign.

My objective is to calculate the ROI per product (with a date slicer), which includes 2 steps:

1) Calculate the cost per session (I assume the cost per session is the same for each product at a selected time period)

Measure 1 = 

DIVIDE(

SUM('24'[common-cost]),

SUM('08'[productSessions])

)

2) Divide the revenue by number of sessions and cost per session

Measure 2 = 

var a = [Measure 1]

return DIVIDE(DIVIDE(SUM('04'[Revenue]),SUM('08 '[productSessions]) ),a)

Measure 1 works fine itself. However I could not get the correct value from Measure 2. It does return values, but none of them are correct. Then I divided the Revenue by number of sessions and calculated ROI (which is the value of Measure 2), and found out that the number differs for each product. 

I have been stuck on this problem for hours and still could not find a way to fix it.

Any suggestion or idea is very much appreciated.

[Greg_Deckler]

Difficult to say exactly given the information but you will need to create a table context for your 1st measure within your 2nd measure that "fixes" the calculation to what you want. You don't really specify what context that is though. Sounds like you need to do a SUMMARIZE by product and then add in your measure to the end of that and then use a FILTER to grab the correct line from your table.

[data_team_afri]

Hi Greg,

Thanks for your answer. I try to put it more clearly.

I created a measure to obtain the average cost during a chosen time period for a chosen country. Given time period and country, this average cost is the same for each product.

Then I need to use the average cost in another measure to calculate ROI per product:  ROI = product revenue/average cost. The ROI varies by product as each product has a different revenue.

As you suggested, I tried to create a new table to store the value of the 1st measure (average cost). I refered to the following link: https://community.powerbi.com/t5/Desktop/Store-measure-output-in-new-table/td-p/410473. However, I found it a bit difficult to apply it because two slicers are involved in this case (time period & country). 

Thanks for your help!