Forum Discussion

SR11's avatar
SR11
Frequent Visitor
7 years ago
Solved

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 ...
  • dedelman_clng's avatar
    dedelman_clng
    7 years ago

    (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 

dedelman_clng's avatar
dedelman_clng
Community Champion
7 years ago

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's avatar
SR11
Frequent Visitor
7 years ago

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

 

Thanks,

SR