Apply % increase / decrease to all numbers in visual based on a column value

[donaldo]

Hey all,

I'm creating a dashboard summarizing the values of a bunch of projects in different way.

Projects are on stages 1-5 according to how far along they are. Their value is budgeted with a % of estimated value based on how far along (likely) they are. So projects at stage 1 have an estimated value of 100, but only 20% of it is expected to take fail rates into account etc. So I would like to create a filter or something similiar that can filter the values of the the entire visual according to their stage, but also show the unconstrained values. Like an on/off.

Project	Stage	Value	Department
A	1	100	X
B	2	100	Z
C	3	100	Y
D	4	100	X
E	5	100	Z

Stage	Budget %
1	10
2	20
3	50
4	70
5	90

What can I do to show the same visuals but with the different values with a toggle of sorts, filter or button?

[AlB]

Hi @donaldo

I do not quite understand what values you would want to show in both cases. Can you explain it a bit more, showing an example based on your sample data?

[donaldo]

hi  @AlB

Thanks for the quick reply.

I've made various charts with the projects depicting their value based on department, date dimensions etc.

I would like to see the very same charts with their value both with and without their % value deductions.

So right now they are all showing the full value, but I would like a toggle so the value of projects at stage 1 only show 10%, stage 2 at 20% and so on. 

[AlB]

@donaldo

You could create a new one-column table Toggle[Toggle] with values ON and OFF (or any other of your liking) and  place it on a slicer. Then create another measure that will do one calculation or the other based on the value of the toggle, which you can harvest through SELECTEDVALUE( 😞

Measure =
SWITCH (
    SELECTEDVALUE ( Toggle[Toggle] ),
    "ON", Write here your code for the ON option ,
    "OFF", Write here your code for the OFF option ,
    Write here your code for what you want with nothing or both options are selected in the slicer
)

There might be a fancier visual for the slicer with button shape or similar. Not sure.  

[donaldo]

Hi @AlB

I've gotten this far, but having trouble setting the ELSE to be same same as the 2nd option. "Unconstrained" which sort of is the defualt view.

How do I wrap up my code (I'm guessing ELSE) so that it will still show values (Unconstrained) if no toggle is selected?

Measure = 
SWITCH (
    SELECTEDVALUE('Confidence Toggle'[Constrained / Unconstrained]);
    "Constrained"; CALCULATE(SUM(Idea[Total Savings Constrained]));
    "Unconstrained";CALCULATE(SUM(Idea[Current Savings]));

[AlB · Accepted solution]

@donaldo

Just as indicated previously.  If you want the unconstrained version if no selection:

Measure =
SWITCH (
    SELECTEDVALUE ( 'Confidence Toggle'[Constrained / Unconstrained] );
    "Constrained"; CALCULATE ( SUM ( Idea[Total Savings Constrained] ) );
    "Unconstrained"; CALCULATE ( SUM ( Idea[Current Savings] ) );
    CALCULATE ( SUM ( Idea[Current Savings] ) )
)

Although you can actually simplify the code a bit like this:

Measure =
SWITCH (
    SELECTEDVALUE ( 'Confidence Toggle'[Constrained / Unconstrained] );
    "Constrained"; CALCULATE ( SUM ( Idea[Total Savings Constrained] ) );
    CALCULATE ( SUM ( Idea[Current Savings] ) )
)

Note that this will show the Constrained version only when only "Constrained" is selected. In all other cases, it will show the Unconstrained version. 

[donaldo]

Hi @AlB

Thanks for the suggestion. I would be okay with a slider option.

I will go ahead and try this solution. 

Will report back.

Thank you for your help.