Forum Discussion
Update columns based on slicer selection
This might be a newbie question but I am trying to find the best way to show data from multiple branches over time. And the end goal is to be able to select one, multiple or all branches in a slicer and update the report.
The underlying data looks like this-
BranchData Table
| Date | Description | Branch1 | Branch2 | Branch3 | Branch4 |
| 8/1/2018 | Net | 100 | 200 | 75 | 150 |
| 8/1/2018 | Rebilling | 25 | 15 | 20 | 35 |
| 8/1/2018 | Customer Billing | 60 | 25 | 45 | 50 |
| 8/1/2018 | Retainage | 25 | 45 | 55 | 85 |
| 7/1/2018 | Net | 120 | 150 | 200 | 100 |
| 7/1/2018 | Rebilling | 15 | 20 | 25 | 15 |
| 7/1/2018 | Customer Billing | 50 | 35 | 100 | 50 |
| 7/1/2018 | Retainage | 25 | 25 | 50 | 25 |
| 6/1/2018 | Net | 200 | 100 | 150 | 100 |
| 6/2/2018 | Rebilling | 55 | 25 | 35 | 15 |
| 6/3/2018 | Customer Billing | 150 | 50 | 100 | 55 |
| 6/4/2018 | Retainage | 35 | 25 | 20 | 45 |
| 5/1/2018 | Net | 100 | 200 | 150 | 100 |
| 5/1/2018 | Rebilling | 15 | 50 | 15 | 35 |
| 5/1/2018 | Customer Billing | 80 | 120 | 85 | 55 |
| 5/1/2018 | Retainage | 10 | 45 | 25 | 20 |
Then I am using a custom table with calculated columns to get some data based on the values above-
CollectionPercent Table
| Date | Branch1 | Branch2 | Branch3 | Branch4 |
| 8/1/2018 | 7.77% | 28.86% | 1.49% | 8.84% |
| 7/1/2018 | 15.10% | 38.95% | 8.55% | 23.11% |
| 6/1/2018 | 18.56% | 35.79% | 9.14% | 22.25% |
| 5/1/2018 | 20.00% | 28.79% | 11.71% | 14.24% |
This table is then being used to build line charts to see data for each branch over time. I would like to add a slicer for Branch and see line charts for one, multiple or all branches. What is the best way to do this? If there is a better was to get to this line chart, then I am open to changing up the way I am getting to the second table. Please advice.
(I will assume the 6/2, 6/3, and 6/4 in your demo are typos)
First, you will want unpivot your data so you have a single combination of Branch and Description on a single line. Otherwise you will have to create a separate measure for each branch (and I assume you have more than 4 branches). In the Query Editor, highlight all of the "branch columns", and on the "Transform" tab click "Unpivot Columns" and "Unpivot Only Selected Columns".
This will get your data in a better format for DAX. You'll probably want to make sure the columns have names that mean something as it defaults to "Attribute" and "Value".
Now you create your formula as a measure (I only have the one table, so you'll have to modify, but this is the pattern you'll want)
Collection Percent = DIVIDE( CALCULATE(SUM(Branches[Value]), Branches[Description] = "Net") - CALCULATE(SUM(Branches[Value]), Branches[Description]="Retainage"), 6)Now create a matrix visual with Date on the rows and BranchNo on the Columns and you'll see the same as your calculated table. Create a slicer for BranchNo (on formatting under "Selection Controls" turn "Show Select All option" to ON and "Single Select" to OFF). Then create a line chart with Axis = Date, Legend = BranchNo, and Value = Collection Percent. The slicer will show only the branches you have chosen (in this example I disabled the interaction between the slicer and the matrix).
One branch
All branches
Two branches
Hope this helps
David
10 Replies
You likely want to use measures instead of your calculated table/columns. Measures automatically recalculate when slicers are applied. A slicer visual can be set up with "Select All" and Multi-select to get 1, group or all branches.
What is the formula for getting the Collection Percent?
Branch1= (CALCULATE(MAX(BranchData[Branch1]),filter(BranchData,BranchData[Date]='CollectionTable'[Date]&&BranchData[Description]="Net")) - CALCULATE(MAX(BranchData[Branch1]),filter(BranchData,BranchData[Date]='CollectionTable'[Dated]&&BranchData[Description]="Retainage")))/6
Please ignore that the values don't match up in the demo tables. I just entered some random data earlier for the sake for visualization.
(I will assume the 6/2, 6/3, and 6/4 in your demo are typos)
First, you will want unpivot your data so you have a single combination of Branch and Description on a single line. Otherwise you will have to create a separate measure for each branch (and I assume you have more than 4 branches). In the Query Editor, highlight all of the "branch columns", and on the "Transform" tab click "Unpivot Columns" and "Unpivot Only Selected Columns".
This will get your data in a better format for DAX. You'll probably want to make sure the columns have names that mean something as it defaults to "Attribute" and "Value".
Now you create your formula as a measure (I only have the one table, so you'll have to modify, but this is the pattern you'll want)
Collection Percent = DIVIDE( CALCULATE(SUM(Branches[Value]), Branches[Description] = "Net") - CALCULATE(SUM(Branches[Value]), Branches[Description]="Retainage"), 6)Now create a matrix visual with Date on the rows and BranchNo on the Columns and you'll see the same as your calculated table. Create a slicer for BranchNo (on formatting under "Selection Controls" turn "Show Select All option" to ON and "Single Select" to OFF). Then create a line chart with Axis = Date, Legend = BranchNo, and Value = Collection Percent. The slicer will show only the branches you have chosen (in this example I disabled the interaction between the slicer and the matrix).
One branch
All branches
Two branches
Hope this helps
David
