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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Dynamic SelectedValue in a calculated Table

So I heard about how PowerBI doesnt support dynamic variables in calculated Columns, so I wanted to ask if anyoe else had a workaround. 

 

Basically, I have a very simple table with two columns. 

1 is Project Name, and 2 is Sales. 

 

My intended endpoint

I want to summarise my tables based on a slicer. Anything with over [x] revenue (determined by slicer) will show Project name. Anything below will be grouped together under "Missed threshold", or "no revenue" or "negative revenue"

 

My current method

1. Create calculated column:

Revised 4A Group =
VAR Threshold = if(HASONEVALUE(List[Value]),VALUES(List[Value]),15000) [I also tried using selectedvalue( instead]
RETURN

Switch(True(),
'4A (PowerBI)'[FY18 Net Revenue]<0,"Negative Revenue",
'4A (PowerBI)'[FY18 Net Revenue]=0,"No Revenue",
'4A (PowerBI)'[FY18 Net Revenue]<Threshold,"Less than Threshold",
'4A (PowerBI)'[Revised TestA])
2. Use summarize function to create a new table summarized based on the calculated column
Summarisetable =
SUMMARIZE(
'4A (PowerBI)',
'4A (PowerBI)'[Revised 4A Group],
"FY18 NR", SUM('4A (PowerBI)'[FY18 Net Revenue]),
"FY18 CM",Sum('4A (PowerBI)'[FY18 Contribution Margin]),
"Rows",COUNTROWS('4A (PowerBI)')
)

 

Issue: The calculated column does not update when I move the slicer as PowerBI does not support that if i recall correctly. Does anyone have a workaround that would allow me to move a slicer, and have the table automatically group revenues that are below 10m into a separate bucket, leaving only the project names of those that meet the criteria showing separately?

 

Thanks so much!

1 REPLY 1
TomMartens
Super User
Super User

Hey,

 

unfortunately what you want to achieve is by no means simple, but nevertheless, there are well documented approaches ...

 

I recommend starting with this excellent blog: http://blog.gbrueckl.at/2019/05/power-bi-dynamic-topn-others-with-drill-down/

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.