TOPN of 3 Other Columns

[bglaugh]

I'm fairly new to PBI so please be gentle. I know Excel and VBA so any DAX input should be fine, I'm just not used to PBI terminology or syntax yet.

So, I have a dataset that is simple, but huge (5million+ rows). It is set up basically like this (not actual data):

Month	Location	Item	Quantity	Cost per item	Overall Cost
January	Building 1	Spade	5	£10	£50
January	Building 1	Bucket	1	£4	£4
January	Building 1	Sponge	4	£0.50	£2
January	Building 1	Cloth	7	£1.50	£10.50
January	Office 2	Cloth	9	£1.50	£13.50
January	Office 2	Bucket	3	£4	£12
January	Office 2	Ladder	4	£15	£60
January	Office 2	Sponge	2	£0.50	£1
January	Office 2	Bag	5	£3	£15
February	Building 1	Bucket	3	£4	£12
February	Building 1	Cloth	4	£1.50	£6
February	Building 1	Ladder	1	£15	£15
February	Building 1	Sponge	7	£0.50	£3.50
February	Office 2	Spade	8	£10	£80
February	Office 2	Bucket	7	£4	£28
February	Office 2	Cloth	2	£1.50	£3
February	Office 2	Vehicle	1	£1000	£1000
February	Office 2	Sponge	1	£0.50	£0.50
February	Office 2	Bag	8	£3	£24

All I need to do I provide the top spend per month per building. I'd like to be able to choose the building from a slicer and provide the following results:

Sicer option: Building 1

Month	Item	Highest Overall Cost
January	Spade	£50
February	Ladder	£15

Slicer option: Office 2

Month	Item	Highest Overall Cost
January	Ladder	£60
February	Vehicle	£1000

I basically understand TOPN and can create a table containing the highest overall spend for the entire dataset (or the top n results), but can't work out how to split it by month or how to slice it by building. It feels like it should be simple.

Any ideas?

 

[schwiggiddy]

If you have a slicer set up for the Location column and a linked Table visual displaying the Month, Item, and Overall Cost columns, you're almost there.

From that, I'd open my Filters pane, filter by the Overall Cost, Top N, enter 1 for my n, and add Overall Cost to the field to rank by under "by value."