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!
