Forum Discussion

cn4422's avatar
cn4422
Helper V
1 year 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 set up the different CPL-Calculations with DAX Measures.

I have the countries in a fact table.

I would now like to create a table "CPL per Country" with the columns "Country" and "CPL".

Then create a relation between Country (fact table) and Country (CPL per Country table).

 

The challenge I'm facing is that I cannot (or don't know how to) insert the CPL for each Country as a measure into the new "CPL per Country" table.

 

I hope I could explain what I would like to accomplish. 😄

 

Thanks for your help/input in advance! 

 

 

Edit:
Solution that worked for me:

 

Create a static table with the country names

Create the switch statement for countries:

 

CPL Switch =
SWITCH(SELECTEDVALUE(Countries[Country]),
"DE",[CPL DE],
"AT",[CPL AT],
etc.
)

 

  • Select a matrix visual and put
  • Countries into row
  • Date into column
  • Switch statemento into value
  • Edit the visual under "values" and select "show values on rows."

 

Result:

 

 

 

 

  • 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)
    )

29 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information or anything not related to the issue or question.

    If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year 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
      1 year 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! 🙂 

    • cn4422's avatar
      cn4422
      Helper V
      1 year ago

      Hi Anonymous ,

      so, finally I'm back with building this report!

       

      Thanks for your suggestions and the file. I checked everything and I think I didn't explain accurtely enough what my challenge is. In your sample report you have the "profit" with respective countries which are part of the "financials" table. I understand how you get relations then.

       

      In my case, however, there are countries, but actually I think it's not relevant. I could also make a new table with a countries column. What I would like to achieve is to then get the measures into this table.

       

      I'm not sure about providing sample data, that's why I'm tryining to explain it as simple as possible in this way. 🙂