Forum Discussion

Oleg222's avatar
Oleg222
Icon for Helper II rankHelper II
4 years ago
Solved

Measures for certain branches

Hello everybody.
There is a list of branches, different indicators are calculated for them. Is it possible to make certain indicators are calculated for certain branches? When a branch was selected by the slicer, were the lines with measures for that branch displayed? I tried to KEEPFILTERS but it leaves the line with the indicator empty, but it needs to disappear.

For example, part of a big table

BranchSum
Branch1, sales   100
Branch1, sales   150
Branch2, sales   200
Branch2, sales   250
Branch3, sales   110
Branch1, expense   50
Branch1, expense   60
Branch2, expense80
Branch3, expense   35

 

Result - sales measure for 3 should not be considered, only expense

MeasuresSum
Branch1    
   Sales   250
   Expense   110
Branch2    
   Sales   450
   Expense   80
Branch3    
   Expense   35

 

 

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    First you should split the Branch column into 2: one for Branch and the other for sales/expenses. You can Use this code in Power Query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcipKzEvOMNRRKE7MSS1WUFBQ0lEyNDBQitXBIWeKLGeEImdkgEcORZ8xqpmGaPalVhSk5hWnQmRN8UmaodkIlQTKWKDZh6LN2FQpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Branch = _t, Sum = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Branch", type text}, {"Sum", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Branch", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Branch.1", "Branch.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Branch.1", type text}, {"Branch.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Branch.1", "Branch"}, {"Branch.2", "Type"}}),
    #"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"Type", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Type", Text.Clean, type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Cleaned Text", each true)
in
    #"Filtered Rows"

    To get this structure:

     Ideally you should then create Dimension Tables for Branch and Type (recommended). This is how the model looks:

     

    Then, with these measures:

    Sum Amount = SUM(FactTable[Sum])
    Exc br3 sales =
SUMX (
    FactTable,
    CALCULATE (
        IF (
            MAX ( 'Dim Branch'[dBranch] ) = "Branch3"
                && MAX ( 'Dim Type'[dType] ) = "sales",
            BLANK (),
            [Sum Amount]
        )
    )
)

    You will get the following:

     

    I've attached the sample PBIX file

     

3 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Please provide sample data or a PBIX file, and a depiction of the expected outcome

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    4 years ago

    First you should split the Branch column into 2: one for Branch and the other for sales/expenses. You can Use this code in Power Query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcipKzEvOMNRRKE7MSS1WUFBQ0lEyNDBQitXBIWeKLGeEImdkgEcORZ8xqpmGaPalVhSk5hWnQmRN8UmaodkIlQTKWKDZh6LN2FQpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Branch = _t, Sum = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Branch", type text}, {"Sum", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Branch", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Branch.1", "Branch.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Branch.1", type text}, {"Branch.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Branch.1", "Branch"}, {"Branch.2", "Type"}}),
    #"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"Type", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Type", Text.Clean, type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Cleaned Text", each true)
in
    #"Filtered Rows"

    To get this structure:

     Ideally you should then create Dimension Tables for Branch and Type (recommended). This is how the model looks:

     

    Then, with these measures:

    Sum Amount = SUM(FactTable[Sum])
    Exc br3 sales =
SUMX (
    FactTable,
    CALCULATE (
        IF (
            MAX ( 'Dim Branch'[dBranch] ) = "Branch3"
                && MAX ( 'Dim Type'[dType] ) = "sales",
            BLANK (),
            [Sum Amount]
        )
    )
)

    You will get the following:

     

    I've attached the sample PBIX file