Update columns based on slicer selection

[SR11]

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. 

[dedelman_clng]

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?

[SR11]

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 · Accepted solution]

(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 

[SR11]

This looks like what I needed! WIll try it out tonight. Thanks a lot!

[SR11]

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.

[SR11]

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. 

[dedelman_clng]

Hi @SR11

If you are confident in your Forecast code (can you post your DAX code?), then to get the prior month is simple

PM Forecast = CALCULATE([Forecast], PREVIOUSMONTH(Branches[Date]))

I have also worked out the Forecast code, but I will save that to see your code first.

Hope this helps

David

[SR11]

Here is  the DAX code for Forecast- 

Forecast = IF
(
SELECTEDVALUE(BranchData[Branch])= "Branch1"||SELECTEDVALUE(BranchData[Branch])= "Branch2"||SELECTEDVALUE(BranchData[Branch])= "Branch3"||SELECTEDVALUE(BranchData[Branch])= "Branch4"||SELECTEDVALUE(BranchData[Branch])= "Branch5",

(CALCULATE(SUM(BranchData[Value]), BranchData[Description]="Net")*.6 + CALCULATE(SUM(BranchData[Value]), BranchData[Description]="Rebilling")*.2),
IF
(
SELECTEDVALUE(BranchData[Branch])= "Branch6"||SELECTEDVALUE(BranchData[Branch])= "Branch7",

(CALCULATE(SUM(BranchData[Value]), BranchData[Description]="Net")*.12 + CALCULATE(SUM(BranchData[Value]), BranchData[Description]="Rebilling")*.52 )
)
)

And I think I figured out my mistake. I was using the same DAX formula for previous month but was referencing a date field from a separate table, so it was not pulling in any results. 

Thank you so much for your assistane with this!!

[dedelman_clng]

If I may, you might not want to have your measures make so much use of hard-coding, as user requirements are likely to evolve over time.

Here is a suggestion on how to make your report resilient to changes in forecasting formulas and addition of branches.

Start by making a "dimension" table out of BranchNo:

Modeling -> Create Table -> Branches = VALUES(BranchValues[BranchNo]).  Make a relationship between this table and the BranchValues table (1 to many on BranchNo)

Create two columns, [Forecast Factor Net] and [Forecast Factor Rebill]. Here you can do a SWITCH or IF() to assign the multipliers for each branch.  Ideally this would be done in your source data (for example, if your source is Excel, just another sheet that has the Branch IDs and their various multipliers), as maintaining the source data is often much easier than going back into PowerBI DAX code when a minor change is needed.

In our example, here are the columns as I used them

Forecast Factor Net = SWITCH(Branches[BranchNo], "Branch1", 0.2, "Branch2", 0.3, "Branch3", 0.4, "Branch4", 0.5)

Forecast Factor Rebill = SWITCH(Branches[BranchNo], "Branch1", 0.6, "Branch2", 0.7, "Branch3", 0.8, "Branch4", 0.9)

You can then write a single measure to calculate the Forecast without any IF logic

Forecast = 
CALCULATE(SUM(BranchValues[Value]), BranchValues[Description]="Net") * MAX(Branches[Forecast Factor Net]) +
    CALCULATE(SUM(BranchValues[Value]), BranchValues[Description]="Rebilling") * MAX(Branches[Forecast Factor Rebill])

 Simliarly, the previous month forecast would be

PM Forecast = CALCULATE([Forecast], PREVIOUSMONTH(BranchValues[Date]))

Please let me know if you have any followup questions.

David

[SR11]

Updating the formulas to so as not to rely on hard coded values makes sense. I appreciate your input.

Thanks,

SR