HIt Rate Calculation

[gunther15]

Hi,

I have a hit range filter of 1 month, 3 months, 6 month, 9 month, and 12 months.  I need to filter out the total trans amount for each one of those to get the hit rate.  I also have the total quoted amount for each month so like January 2020 etc.  So for 1 month I need the 1 month hit range trans amount divided by the quoted amount in that specific month.  For 3 months I would need 1 and 3 Months added together divided by the total quoted amount for that specific month and etc.  What caculations would I needed to be done to get this?

Here is the data for January 2020.

Thank you

[v-yiruan-msft · Accepted solution]

Hi  @gunther15 ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

1. Add a custom column as below in Power Query Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY47CgMxDESvYlwvQj/L9iHCHsC4SyBFyMIm9yeyXSxEqBAzjxm1FuM21siEEse+tUjhdry/T1cVURiK+ClMVpbNy/8MVZkNWP0kQqQ/Ihxn2F/3xzn9ipYgjzAqaLbK5Aor4gOuDzhrxQnYBaRcNUOabVyNbQL1ArIQM2gaALI/FHv/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Hit Rate Range" = _t, #"Total Trans Amount" = _t, #"Total Quoted" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Hit Rate Range", type text}, {"Total Trans Amount", type number}, {"Total Quoted", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Month Range", each if [Hit Rate Range]="" then 0 
else if  Text.Contains ([Hit Rate Range],"or Older") then 999 else  Text.Select([Hit Rate Range],{"0".."9"})),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Month Range", Int64.Type}})
in
    #"Changed Type1"

2. Create a measure as below:

Hit Rate = 
VAR _selmrange =
    SELECTEDVALUE ( 'Table'[Month Range] )
VAR _sumoftquoted =
    SUM ( 'Table'[Total Quoted] )
VAR _cultramount =
    CALCULATE (
        SUM ( 'Table'[Total Trans Amount] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Month Range] <= _selmrange )
    )
RETURN
    DIVIDE ( _cultramount, _sumoftquoted )

If the above one can't help you get the desired result, please provide more raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards