Dear all
just to collect some thoughts if that is a good way or if this formula will slow down my pbi report in future...
I have two calculated values (Revenue/Units) and a calculated average (ASP). The user can choose from a button which value he wants to see. That works fine and fast with the switch function if i have one variable sum. Now i want to add targets as a second dimension, so i created the following formula:
var_Act vs. Target % = Switch( True();
values(dim_measureswitch[Measure]) = "Revenue"; [var_month_line_chart LC]/[var_Target LC]-1;
values(dim_measureswitch[Measure]) = "Units"; [var_month_line_chart LC]/[var_Target LC]-1;
values(dim_measureswitch[Measure]) = "ASP"; [var_month_line_chart LC]/[var_Target LC]-1;
blank())
var_Target LC = Switch( True();
values(dim_measureswitch[Measure]) = "Revenue"; [Target Sales LC];
values(dim_measureswitch[Measure]) = "Units"; [Target Unit];
values(dim_measureswitch[Measure]) = "ASP"; divide([Target Sales LC];[Target Unit];0);
blank())
var_month_line_chart LC = Switch( True();
values(dim_measureswitch[Measure]) = "Revenue"; ([Sales Revenue LC]-[MTD Sales Revenue LC])+ [MTD Sales Revenue LC RR CD];
values(dim_measureswitch[Measure]) = "Units"; ([Sales Units]-[MTD Sales Units])+ [MTD Sales Units RR];
values(dim_measureswitch[Measure]) = "ASP"; divide(([Sales Revenue LC]-[MTD Sales Revenue LC])+ [MTD Sales Revenue LC RR CD];([Sales Units]-[MTD Sales Units])+ [MTD Sales Units RR];0);
blank())
As you see i have now two variables (one with revenue (var_month_line_chart) and one with targets (var-target LC). That works fine, but the speed is decreasing.
What do you think is this the right way or is there a more efficient way to do this calculation?
KR
Florian
