cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

Calculated measure to get the vendors with most overall appearances in the top 3

Hi All,

Hope everyone's good.

I need to create a measure to get the top 3 vendors with most appearances in each month in the "top highest sum amount by month". I need a view where I can monitor these vendors that are consistent in the top 3 each month. This is going to be helpful so I can flag these vendors and report.

I have 4 columns:

Date column: January to April 2023

Vendor name column: A, B, C, D, E

Amount column

Discount column: Yes or No (Choose Yes only)

FYI- Please filter Discount column with "Yes" and then refer to the amount.

Perhaps something like this:

Here's the sample pbix if you need it. Thank you

1 ACCEPTED SOLUTION
Super User

Top 3 =
var a = SUMMARIZE(Discount,[Vendor name],"am",sum(Discount[Amount]))
return if(hasonevalue(Discount[Vendor name]),BLANK(),CONCATENATEX(topn(3,a,[am],DESC),[Vendor name],",",[am],DESC))

see attached

2 REPLIES 2
Super User

what's the expected output based on the sample data you provided?

Proud to be a Super User!

Super User

Top 3 =
var a = SUMMARIZE(Discount,[Vendor name],"am",sum(Discount[Amount]))
return if(hasonevalue(Discount[Vendor name]),BLANK(),CONCATENATEX(topn(3,a,[am],DESC),[Vendor name],",",[am],DESC))

see attached

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.