Forum Discussion
Update columns based on slicer selection
(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
Community ChampionYou 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.
- dedelman_clng
(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
This looks like what I needed! WIll try it out tonight. Thanks a lot!
