Dynamic Slicers for Bar and Line Chart

[hbrandol]

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.

[v-gizhi-msft · Accepted solution]

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