Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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!

Need Help