Forum Discussion
divison based on period
starting from left to right - 1st column is Period 2nd is type then , time period and month 1 and month 2 data.
i want a dax formula to divide lunch total of month 1 by total of lunch and afternoon. similarly for month 2.
FOR EXAMPLE : let sum of Lunch be 100 and sum of afternoon be 50 for month 1, so formula should divide 100/150, and similarly 50/150. basically i want each type's contribution to the total.
Hi Anonymous
If you want to implement it in power query, you can put the following code to advanced editor in power query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZE7TgNBEETvsrHd6v8nJLfEASxHyIjEawvB/ZneRWR4oilp6k1X9ZzPiy+H5fS9vn2M8/VxXY/8uA3J5GjAfcsWKCC6XA7/2kmdFdRaerGAxnN7lINkS3MTQHpmF85IqGqpngVaT+3GJFDRsjgIyv7sL+9f18/1fl+72ON2tN8BTYS2HHQA64QgEUnI2hoIjXQTgCkwALmloCIoT0OlOyhtoaKk4ckMLxsz+hfGCGOgmKYyVIj+CNbBwGxTVJEKSRvLaiBTwjxqr0Fiib20WSilAO8azDjgmBFEig77iMiAaW2P8azvi8pwSJkRI1IBygYjI2Aul8sP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, Type = _t, #"Time Period" = _t, Month1 = _t, Month2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", Int64.Type}, {"Type", type text}, {"Time Period", type text}, {"Month1", type number}, {"Month2", type number}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Period", "Type"}, {{"SumM1", each List.Sum([Month1]), type nullable number}, {"Sum_M2", each List.Sum([Month2]), type nullable number}, {"Data", each _, type table [Period=nullable number, Type=nullable text, Time Period=nullable text, Month1=nullable number, Month2=nullable number]}}), #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Time Period", "Month1", "Month2"}, {"Time Period", "Month1", "Month2"}), #"Reordered Columns" = Table.ReorderColumns(#"Expanded Data",{"Period", "Type", "Time Period", "Month1", "Month2", "SumM1", "Sum_M2"}), #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Per_M1", each [SumM1]/List.Sum(List.Distinct(#"Reordered Columns"[SumM1]))), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Per_M2", each [Sum_M2]/List.Sum(List.Distinct(#"Reordered Columns"[Sum_M2]))), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"SumM1", "Sum_M2"}) in #"Removed Columns"Output
If you want to use dax, you can refer to the following measures
%M1 = VAR a = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), [Period] IN VALUES ( 'Table'[Period] ) && [Type] IN VALUES ( 'Table'[Type] ) ), [Month1] ) RETURN DIVIDE ( a, SUMX ( FILTER ( ALLSELECTED ( 'Table' ), [Period] IN VALUES ( 'Table'[Period] ) ), [Month1] ) )%M2 = VAR a = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), [Period] IN VALUES ( 'Table'[Period] ) && [Type] IN VALUES ( 'Table'[Type] ) ), [Month2] ) RETURN DIVIDE ( a, SUMX ( FILTER ( ALLSELECTED ( 'Table' ), [Period] IN VALUES ( 'Table'[Period] ) ), [Month2] ) )Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Anonymous
If you are looking for a DAX solution then, use the following and modify your column names. use this measure on a visual where you have already added to your visual.
PercentageOfTotal =
divide(
sum(table[month1]),
Calculate(
sum(table[month1]) ,
allselected ( table1[type] )
)
)Hi Anonymous
If you want to implement it in power query, you can put the following code to advanced editor in power query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZE7TgNBEETvsrHd6v8nJLfEASxHyIjEawvB/ZneRWR4oilp6k1X9ZzPiy+H5fS9vn2M8/VxXY/8uA3J5GjAfcsWKCC6XA7/2kmdFdRaerGAxnN7lINkS3MTQHpmF85IqGqpngVaT+3GJFDRsjgIyv7sL+9f18/1fl+72ON2tN8BTYS2HHQA64QgEUnI2hoIjXQTgCkwALmloCIoT0OlOyhtoaKk4ckMLxsz+hfGCGOgmKYyVIj+CNbBwGxTVJEKSRvLaiBTwjxqr0Fiib20WSilAO8azDjgmBFEig77iMiAaW2P8azvi8pwSJkRI1IBygYjI2Aul8sP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t, Type = _t, #"Time Period" = _t, Month1 = _t, Month2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", Int64.Type}, {"Type", type text}, {"Time Period", type text}, {"Month1", type number}, {"Month2", type number}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Period", "Type"}, {{"SumM1", each List.Sum([Month1]), type nullable number}, {"Sum_M2", each List.Sum([Month2]), type nullable number}, {"Data", each _, type table [Period=nullable number, Type=nullable text, Time Period=nullable text, Month1=nullable number, Month2=nullable number]}}), #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Time Period", "Month1", "Month2"}, {"Time Period", "Month1", "Month2"}), #"Reordered Columns" = Table.ReorderColumns(#"Expanded Data",{"Period", "Type", "Time Period", "Month1", "Month2", "SumM1", "Sum_M2"}), #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Per_M1", each [SumM1]/List.Sum(List.Distinct(#"Reordered Columns"[SumM1]))), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Per_M2", each [Sum_M2]/List.Sum(List.Distinct(#"Reordered Columns"[Sum_M2]))), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"SumM1", "Sum_M2"}) in #"Removed Columns"Output
If you want to use dax, you can refer to the following measures
%M1 = VAR a = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), [Period] IN VALUES ( 'Table'[Period] ) && [Type] IN VALUES ( 'Table'[Type] ) ), [Month1] ) RETURN DIVIDE ( a, SUMX ( FILTER ( ALLSELECTED ( 'Table' ), [Period] IN VALUES ( 'Table'[Period] ) ), [Month1] ) )%M2 = VAR a = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), [Period] IN VALUES ( 'Table'[Period] ) && [Type] IN VALUES ( 'Table'[Type] ) ), [Month2] ) RETURN DIVIDE ( a, SUMX ( FILTER ( ALLSELECTED ( 'Table' ), [Period] IN VALUES ( 'Table'[Period] ) ), [Month2] ) )Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
