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
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
This looks like what I needed! WIll try it out tonight. Thanks a lot!
Ok, so I got most of my charts up and ready. There is just one set of values needed for forecasting that I am not able to generate.
So here are my follow up questions-
1. How do I get values for branches and that need different set of calculations? For instance I need a value called Forecast that I was previously calculating with the following formula(different for branches)-
Branch1Forecast =
CALCULATE(MAX(BranchData[Branch1]),filter(BranchData,BranchData[Date]='CollectionPercent'[Date]&&BranchData[Description]="Net"))*0.6 + CALCULATE(MAX(BranchData[Branch1]),filter(BranchData,BranchData[Date]='CollectionPercent'[Date]&&BranchData[Description]="Rebilling"))*0.2
Branch2Forecast =
CALCULATE(MAX(BranchData[Branch2]),filter(BranchData,BranchData[Date]='CollectionPercent'[Date]&&BranchData[Description]="Net"))*0.8 + CALCULATE(MAX(BranchData[Branch2]),filter(BranchData,BranchData[Date]='CollectionPercent'[Date]&&BranchData[Description]="Rebilling"))*0.3
If you notice the calculations vary for each branch.
How best to achieve this using Measure with unpivoted data?
2. How to get last month's value of a forecast for a branch?
The formula I was using previously is
Branch1LastMForecast =
VAR Previous_Month =
MAXX (
FILTER ( 'CollectionPercent', 'CollectionPercent'[Date] < EARLIER (' CollectionPercent'[Date] ) ),
'CollectionPercent'[Date]
)
RETURN
CALCULATE (
SUM ( 'CollectionPercent'[Branch1Forecast] ),
FILTER ( 'CollectionPercent', 'CollectionPercent'[Date] = Previous_Month )
)Please advice. Again, thank you so much for your help.
So I figured out how to get the Branch Forecast using SELECTEDVALUE. But I am struggling with getting last month's value based on the Branch Forecast measure.