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 ...
  • PaulDBrown's avatar
    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