Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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.
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?
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.
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.
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]));
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |