Help with average formula

[trwatts]

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. 

[v-eqin-msft · Accepted solution]

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] ) )

Here is the pbix file.

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

[Fowmy]

@trwatts 

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 🙂

YouTube, LinkedIn

[trwatts]

@Fowmy

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 2

Service 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