Forum Discussion
Help with average formula
Hi Everyone,
I'm stumped and I am sure this is easy to do but my head has stopped operating.
I have two tables, Table one contains three columns that contain the number of business days in the month we operate for each booking type. We can call them Type A, B, and C.
Table Two includes a list of bookings for the month, the data includes which booking type the booking was for in a single column.
What I want to do is divide the number of bookings in the month in table two by the number of business days from table one. The formula needs to take into account the booking type.
I have a slicer to separate the booking types that needs to update the result.
I will be displaying the result in a line chart.
Any help would be great.
Hi trwatts ,
According to my understand, you want to get the business days based on the same Type and same month in two tables, right?
You could apply these stpes to transform Table1:
1. Select Month Days in Month column -->unpivot other columns
2. Replace "type" and "Days in Month" with blank
3. Lower and trim the left characters
The whole M syntax and the final output of Table1 are show below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixN1zW0UNJRMgBiI2MwI1YnWik4tQBJ3AAu7p9cAhG3BGJDIxABFvfLL0NSbwRX75KaDBEHKzUGM0DiXol5uoZgMyyhkhBxt9QkiDiavb6JRckZSDKGcBnHgqLMHIgMyB5DkIyxUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Month Days in Month" = _t, #"type A Days in Month" = _t, #"type B Days in Month" = _t, #"type C Days in Month" = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each "2018 "&[Month Days in Month]), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}, {"type A Days in Month", Int64.Type}, {"type B Days in Month", Int64.Type}, {"type C Days in Month", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Month Days in Month"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Month Days in Month"}}), #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Month Days in Month", "type A Days in Month", "type B Days in Month", "type C Days in Month"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Month Days in Month"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Days in Month","",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","type","",Replacer.ReplaceText,{"Attribute"}), #"Lowercased Text" = Table.TransformColumns(#"Replaced Value1",{{"Attribute", Text.Lower, type text}}), #"Trimmed Text" = Table.TransformColumns(#"Lowercased Text",{{"Attribute", Text.Trim, type text}}) in #"Trimmed Text"Now you could use the following formula to calculate the needed values:
days = LOOKUPVALUE ( 'Table1 (2)'[Value], [Month Days in Month].[MonthNo], 'Table2'[Date Booked].[MonthNo], 'Table1 (2)'[Attribute], 'Table2'[Session Type] )sumCost = CALCULATE ( SUM ( 'Table2'[Cost] ), ALLEXCEPT ( 'Table2', Table2[Date Booked].[MonthNo], 'Table2'[Session Type] ) )Measure = DIVIDE ( MAX ( Table2[sumCost] ), MAX ( 'Table2'[days] ) )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Eyelyn Qin
3 Replies
Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?
_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.Click on the Thumbs-Up icon if you like this reply 🙂
I went to put the data in as a table but it wouldn't let me? I have added commas where there would normally be a column.
Data set 1
Month Days in Month, type A Days in Month, type B Days in Month, type C days in month
Aug-18, 0, 23, 0
Sep-18 0, 20, 0,
Oct-18 9 13 1
Nov-18 0 22 0
Dec-18 2 13 2
Jan-19 19 2 2
Feb-19 0 20 0
Mar-19 0 21 0
Apr-19 8 11 3
Table 2Service Name, Date Booked, Cost, Service ID, Session Type
Location A, 3/07/2018, $23.00, 8562, a
Location A, 4/07/2018, $26.00, 8562, b
Location A 24/07/2018 $26.00 8562 b
Location A 25/07/2018 $26.00 8562 b
Location A 31/07/2018 $31.00 8562 c
Location B 1/08/2018 $26.00 8562 b
Location B 7/08/2018 $31.00 8562 c
Location B 8/08/2018 $23.00 8562 a
Location C 14/08/2018 $23.00 8562 a
Location C 15/08/2018 $23.00 8562 a
Location C 21/08/2018 $31.00 8562 c
Location C 22/08/2018 $26.00 8562 b
Location C 28/08/2018 $26.00 8562 b
Location C 29/08/2018 $26.00 8562 b
Location D 4/09/2018 $31.00 8562 c
Hi trwatts ,
According to my understand, you want to get the business days based on the same Type and same month in two tables, right?
You could apply these stpes to transform Table1:
1. Select Month Days in Month column -->unpivot other columns
2. Replace "type" and "Days in Month" with blank
3. Lower and trim the left characters
The whole M syntax and the final output of Table1 are show below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixN1zW0UNJRMgBiI2MwI1YnWik4tQBJ3AAu7p9cAhG3BGJDIxABFvfLL0NSbwRX75KaDBEHKzUGM0DiXol5uoZgMyyhkhBxt9QkiDiavb6JRckZSDKGcBnHgqLMHIgMyB5DkIyxUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Month Days in Month" = _t, #"type A Days in Month" = _t, #"type B Days in Month" = _t, #"type C Days in Month" = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", each "2018 "&[Month Days in Month]), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type date}, {"type A Days in Month", Int64.Type}, {"type B Days in Month", Int64.Type}, {"type C Days in Month", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Month Days in Month"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Month Days in Month"}}), #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Month Days in Month", "type A Days in Month", "type B Days in Month", "type C Days in Month"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Month Days in Month"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Days in Month","",Replacer.ReplaceText,{"Attribute"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","type","",Replacer.ReplaceText,{"Attribute"}), #"Lowercased Text" = Table.TransformColumns(#"Replaced Value1",{{"Attribute", Text.Lower, type text}}), #"Trimmed Text" = Table.TransformColumns(#"Lowercased Text",{{"Attribute", Text.Trim, type text}}) in #"Trimmed Text"Now you could use the following formula to calculate the needed values:
days = LOOKUPVALUE ( 'Table1 (2)'[Value], [Month Days in Month].[MonthNo], 'Table2'[Date Booked].[MonthNo], 'Table1 (2)'[Attribute], 'Table2'[Session Type] )sumCost = CALCULATE ( SUM ( 'Table2'[Cost] ), ALLEXCEPT ( 'Table2', Table2[Date Booked].[MonthNo], 'Table2'[Session Type] ) )Measure = DIVIDE ( MAX ( Table2[sumCost] ), MAX ( 'Table2'[days] ) )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Eyelyn Qin
