cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
data_team_afri Frequent Visitor
Frequent Visitor

refer the value of a measure as static in another measure

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.
 

image.png

 

 

 

 

 

 

 

2 REPLIES 2
Super User
Super User

Re: refer the value of a measure as static in another measure

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Highlighted
data_team_afri Frequent Visitor
Frequent Visitor

Re: refer the value of a measure as static in another measure

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!

 

 

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 6 members 2,564 guests
Please welcome our newest community members: