Compare TopN Doctor Specialty's visits for current financial year to previous financial years

[naveenpadala]

I have Financial specialties, which have patient visits(PPD) for each Fiscal Year(FY). I want to get the TopN Financial specialties for current FY (i.e 2023 present) and compare the same Financial specialties for previous FY (i.e, 2022, 2021 etc). When i tried the TopN for previous FY, it doesnot show the same Financial specialites. 

Please find the raw data and expected results sheet in below link:
https://docs.google.com/spreadsheets/d/1-DeCrhc8KCcpycchOymxBueAsJvub3Tu/edit?usp=share_link&ouid=11... 

[alena2k · Accepted solution]

hi @naveenpadala 

You can Pivot year column in Power Query and then use TopN for the year you need.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVhLb+NGDP4rQc57GA45Q/LYdoOgwOaBbbfAItiD6qixAMcOZKdo/n0pO1KGsmQvcglEzpD8+PExfni4/Fyvd9Xq8tMl55yIlKP9H0PEyx+fHi5/WVf1drest81mtXl6M1EOHFI46MCcjhICH3TCXud++7ZYNtWu7cSYo4kVKR9U4vs1j21/QSjPTggOhu/MaNt9gEQQglAs73MmE3QKLFIG97V6HFyOgCwxBpxTEMwiCjF5n0eRpxiSgEbv/5GSRa/oYvmtaj9sISahrOUlTk5BoqCW56/rdd1Wq4v7tlrsmkW9N4PEHKCMaEKNydIR+ttm4sKUFDNxqTQwJ+eYKGRml5zOTLV+vLip/mtWq80/1aKxL3+8tk/1gQWGuB1y7g1XAgNRQlByLKp2yyFhkg3p3iP8OZsxZciK4MNon6tdfy0ko0oMjrwjjQzADLEM9nPzZIA1/9aFLQDMaqk+SYXIRqsg6XTNASVKiVyKJk1il6QopWtXVfvp4nazrffA/LlsN9WuI0cmySH8jKYFy72H73n49uXm7vbuy9319z0gqJx93ygzZZmUlBOW3l891+b0evF2cVM/Notm3TFxrwAyrwCHyEDnVaQDaV6MRBRcdf5VbRevq6otYSTKkkHPOqxWOYIlhverypKyKC9LVoUioUTnav24WbTNukfIitQ0cunWWMXalyYFZ2yaAhytctRV1aSeMMUkZYwTzSGmxF0vOoeYNfScwRXjqGqMmNlYcsaeRKWumUy1tjJBbH9yRsmGguZwElTEYNx0Lf1uvRjE1sgx+NT9vt7V7doMlbQ0f7JKT7wpFUyRhPSkM0A5ox9AU1clzMGSd4Z1GA1JcjV3W78sh0Eao0EdHTq39Wu72X5cEazB5uT6cwlOBPuqcdYCJYkppNLPvYV+CBJZwMcO9GLO1opdikddx/DSyGG+lTPHaCOw9H8UIrNQ4Hzk4oeG2CaTEU/ApGwFgg7ob0MUWbgTOwh/vf5+28FjCQpAbmQOIqZhsoVTvdm6Kobsin1OUxKquGyVybTlKGk/q+BYzMIQ3Cy+e1nultXqeQgVCAc64BgHoDhQKU6ej4pRVU+ZMAVIfmaVlMmBYiBnxImNr6yezuf2BbBdIev72Om9Ortk2LjOrI5Wk4XMas3VEXhSLdqSAOT9tun6UnU62+67bduS3UIy1kiBrQO7MHqysXb3u9QMIhIBl1K/hloEw5hEt5KbO6jZN7xe1mUJ7aQDaJxrm5lRxYFz34Vj+/whJKBgMHtK+qAxgiDjvPuWWSA5YcKiR3Sd1nU45ASY3CPG91g2u+yqv9yLJAQYlfk4Aqs5lZGHxWLF0VAKfhT4CLLlNjkI7pdvtlh2qahejp5a49Oi7B9pU2NWbVGnUyZMnKPw2Xlt+/4o4xNzDUNI7Lg+qdU1O0eO6YUyodhG6Jwvn42U9hTyT7GRho1jezO4JBxv8H79m1xcbR0HV4Z7O5Mv4ilJb/norTz7usaJqSr2aBT24XoFe7FlcmNreLQ5g321e2PVamXIdauolWI9FpfvbSSbrKTqf0aYOx+PzysEtEVY/A4xzf7x3KZu0KCLcgrzo3Fv0wKSa7MzBnFuk1botmQ5Z3n2PNhrwxxzGfxaL6u/m1WzM+Q265J0H3H8+B8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Financial Specialty Grouping" = _t, PPD = _t, FY = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Financial Specialty Grouping", type text}, {"PPD", Int64.Type}, {"FY", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [FY] <> null),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Filtered Rows", {{"FY", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Filtered Rows", {{"FY", type text}}, "en-US")[FY]), "FY", "PPD", List.Sum)
in
    #"Pivoted Column"

Note, you will need to clean data for the records with missing FY - I simply filtered it before pivoting.

Here is a link to download .pbix file.