Forum Discussion
Calculate column based on slicer
Hello
I need some help to create a solution...
This is my table:
| City | Population | Var1 | Var2 | Final |
| city1 | 100 | 1.2 | 1.5 | X |
| city2 | 200 | 1.2 | 1.1 | Y |
Can I use slicer to calculate the "final" value?
So, in my report I only will use columns City, Population and Final (calculated by slicer's item * Population).
Any Idea? I'm searching in community but I didn't find anything yet.
Hi Anonymous,
What item did you add to slicer?
If the slicer value is numeric and comes from a field from an other table without any relationship to above table, like below:
Measure for final value could be:
Final1 = MAX(Table2[Rate])*MAX(Table1[Population])
If your slicer shows column names as slection, like "Var1", "Var2", then, please consider below two methods.
Method 1:
Create an extra table (in my test, it's Table3) which lists all available column names. Add 'Table3'[Variable] into slicer.
Measure:
Final2 = SWITCH ( LASTNONBLANK ( Table3[Variable], 1 ), "Var1", MAX ( Table1[Population] ) * MAX ( Table1[Var1] ), "Var2", MAX ( Table1[Population] ) * MAX ( Table1[Var2] ) )
Method 2:
First Pivot original table in Query Editor mode.
Measure:
Final3 = MAX(Table1[Population])*MAX(Table1[Value])
Add 'Table1'[Variable] to slicer.
Best regards,
Yuliana Gu
5 Replies
v-yulgu-msftMicrosoft Employee
Hi Anonymous,
What item did you add to slicer?
If the slicer value is numeric and comes from a field from an other table without any relationship to above table, like below:
Measure for final value could be:
Final1 = MAX(Table2[Rate])*MAX(Table1[Population])
If your slicer shows column names as slection, like "Var1", "Var2", then, please consider below two methods.
Method 1:
Create an extra table (in my test, it's Table3) which lists all available column names. Add 'Table3'[Variable] into slicer.
Measure:
Final2 = SWITCH ( LASTNONBLANK ( Table3[Variable], 1 ), "Var1", MAX ( Table1[Population] ) * MAX ( Table1[Var1] ), "Var2", MAX ( Table1[Population] ) * MAX ( Table1[Var2] ) )
Method 2:
First Pivot original table in Query Editor mode.
Measure:
Final3 = MAX(Table1[Population])*MAX(Table1[Value])
Add 'Table1'[Variable] to slicer.
Best regards,
Yuliana Guthank you!
I guess the second solution is better to me.
If I set Final3 = MAX(Table1[Population])*MAX(Table1[Value]), It always use the max value, ignoring my filter, can I remove the MAX function?
Other thing, how can I set a default variable, for example, if I click two times at same slicer's item, use first item in Final3.
- TomMartens
Super User
Hey,
enclose each of your parts into a calculate like so CALCULATE(...) * CALCULATE()
Guess this helps
- v-yulgu-msft
Hi Anonymous,
Have you achieved your requirement? If yes, would you please mark the corresponding reply as an answer or share your solution so that it can benefit more users?
Regards,
Yuliana Gu
