Forum Discussion
Oleg222
Helper II
4 years agoMeasures 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 ...
- 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
PaulDBrown
Community Champion
4 years agoFirst 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