Forum Discussion
Microsoft EmployeeDynamic Slicers for Bar and Line Chart
I want to create a dynamic slicers for a line and stacked column chart.
The line is the ROI values for each product by months and the column is the Total Loan values for each product by months.
ROI is a measure based on sum of interest/sum of total loan; for each product
Total Loan is a measure based on sum of total; for each product
My goal is when the user click on the slicer, the column and line related to that product will be chosen.
Here what I have done so far:
1. Created the slicer table
Mortgage Loan
Auto Loan
Student Loan
2. Created the ROI table
| ROI | Max |
| ROI Mortgage Loan | 1 |
| ROI Auto Loan | 2 |
| ROI Student Loan | 3 |
3. Created Total Loan Table
| Total Loan | Max |
| Mortgage Loan | 1 |
| Auto Loan | 2 |
| Student Loan | 3 |
4. Created the following measures:
ROI = SWITCH([Max ROI],1,[ROI Mortgage Loan],2,[ROI Auto Loan],3,[ROI Student Loan])
Max ROI = MAX('ROI Table'[Max])
Total Loan = SWITCH([Max Total Loan],1,[Mortgage Loan],2,[Auto Loan],3,[Student Loan])
Max Total Loan = MAX('Total Loan Table'[Max])
I cant figure out how to make the slicer works. I cant figure out how the slicer should have both components the ROI and Total Loan.
Hi,
According to your description, i create a table to test:
Then, create two measures:
ROI = SUM('Table'[Interest])/SUM('Table'[Loan values])Total Loan = SUM('Table'[Loan values])Choose line and stacked column chart, then set a slicer, and it shows:
Hope this helps.
Best Regards,
Giotto Zhi
1 Reply
- v-gizhi-msft
Community Support
Hi,
According to your description, i create a table to test:
Then, create two measures:
ROI = SUM('Table'[Interest])/SUM('Table'[Loan values])Total Loan = SUM('Table'[Loan values])Choose line and stacked column chart, then set a slicer, and it shows:
Hope this helps.
Best Regards,
Giotto Zhi