Forum Discussion

Cris0410's avatar
Cris0410
New Member
3 years ago

Need help with creating a dynamic column in Power BI

Hello, I'm trying to find a way to turn a measure into a filter (I know I can't add a measure as a filter) and that's why I'm asking for help because I haven't found a way to create a dynamic column that can vary depending on the selected date.

 

The table is divided into columns of clicks, daily sales, ACOS, and dates, and the mentioned measure has the following formula to know the performance of the search terms of my campaigns.

 

Performance =

IF (AND ([Clicks] >= 10, [Total Sales per Day] = 0), "Bleeder",

IF (AND ([Clicks] < 10, [Total Sales per Day] = 0), "Untested",

IF ([ACOS] > 0.25, "Unprofitable",

IF (AND ([ACOS] >= 0.15, [ACOS] < 0.25), "Profitable**",

IF ([ACOS] < 0.15, "Profitable", BLANK())))))

 

The problem is that when I filter a word by a date range, the same word appears in the "Bleeder" and "Profitable" category, for example, because it takes into account the daily result and does not make the total sum...

 

If anyone can help me, I would really appreciate it.

Thank you!

Need Help

2 Replies

  • olgad's avatar
    olgad
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    Hi, it is hard to follow what you would like to achieve. Can you post the sample data and the output you want versus what you get? May be field parameters can help.

    • Cris0410's avatar
      Cris0410
      New Member
      3 years ago

      I have data organized in several columns, such as:

      • Dates
      • Search terms
      • Clicks
      • ACOS
      • Daily sales

      When I add a column with a formula, the result is incorrect when I filter by a date range, because the formula performs daily calculations instead of totals for the selected date range and search term in the filter. In the attached image, you can see that when I filter for the search term "plantillas de gel mujer" in a certain date range, the matrix shows correct results for the selected date range as "Profitable**", but in the filter, it shows "Profitable" and "Untested", which is incorrect as it should only display "Profitable**".

       

      Clearly, I have a measure in the matrix and the column with the aforementioned formula in the filters.

      Therefore, I am not sure how to get it to display the correct result.