Forum Discussion

cn4422's avatar
cn4422
Helper V
2 years ago
Solved

Create a relationship between Fact Table Values and Measure Values

Hi,   I have attached a screenshot of what I would like to accomplish.   The idea is to create CPL (Cost-per-Lead) for different countries and display them over a certain time period.   I have ...
  • lbendlin's avatar
    lbendlin
    1 year ago
    CPL FB =
    SWITCH(SELECTEDVALUE(Countries[Country]),
    "DE",DIVIDE([Sum Spend DE FB],[Sum Leads DE FB],0),
    "AT",DIVIDE([Sum Spend AT FB],[Sum Leads AT FB],0)
    )
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi cn4422 

 

Thanks for the reply from lbendlin .

 

cn4422 , based on your current description, are you trying to put the Country column and measure into one table and create a relationship with the Country column in the original table? If so, please refer to the following test. If this sample data is structurally different from the one you are using, as lbendlin said, we need you to provide some sample data so that we can help you better. Currently I am using sample data from Power BI Desktop.

 

1. Create a measure as follows

sum = CALCULATE(SUM(financials[Profit]), ALLEXCEPT(financials, financials[Country]))

 

2. Create a calculated table as follows

sum per country = SUMMARIZE(financials, financials[Country], "sum", [sum])

 

3. Create a relationship between two tables

 

Best Regards,
Yulia Xu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

cn4422's avatar
cn4422
Helper V
2 years ago

Thanks for your help. I'm currently building another report which is more urgent - but I hope I will get back to this one by the end of the week to see if your suggestion will do the trick! 🙂