Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Cris0410
New Member

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!

2 REPLIES 2
olgad
Super User
Super User

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.


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

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.

 

Example measure vs filter.jpg

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors