Forum Discussion
HIt Rate Calculation
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
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
1 Reply
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