How to set a filter based on max value of each category

[diegomengue]

Hello.

I have a small sales dataset with date, salesman, quantity and customer, a related data table and another related table with my customers data (name, category - as in market, gas station, hotels, etc., location, etc.).

I want my end user to be able to select a date period (with a date slicer) and be able to find, for each category, which customer is the best buyer. For example: between 20 markets, 15 gas stations and 10 hotels, I need to know that the best ones aremarket A,  who sold 100 units, gas station B who sold 80 units and hotel C who sold 3 units in that time frame...

I also need to use those categories max values to evaluate if another same-category customer is doing good or not: in that same example, if market A has sold 100 units but market Y has sold 50 (some fixed parameter, like less than 70% max), then he is doing bad in that period.

Is it possible?

[amitchandak]

@diegomengue , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

You can create a measure like

rankx(filter(allselected(Table[customer], table[category]), table[category] =max(table[category])), [sales])

and filter Rank =1

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

https://www.youtube.com/watch?v=DZb_6j6WuZ0
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

[diegomengue]

Example:

Sales table:

Date	Customer	Quantity
01/01/2021	A	12
01/01/2021	B	15
02/01/2021	A	5
02/01/2021	C	8
03/01/2021	D	4
04/01/2021	B	10
04/01/2021	D	6

Customer table:

A	Category 1
B	Category 1
C	Category 2
D	Category 2

So what I want to know is, for period 01/01/2021 - 04/01/2021

Category 1	Customer B (25 units)
Category 2	Customer D (10 units)

I want to know which customer is the most buying one for each category, and I also would like to filter with this number... so, for example: for Category 2, I would like to see which clients had Total Units Sold <= (0.7 * Customer D sum).

Thanks @amitchandak .

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[diegomengue]

It worked, thank you!!!

I'd also like to use this "25" to rank the other customers on category 1.: if customer B is my best customer on Category 1, it equals 100%... I want to know the percentage of B my other customers represent.

So, if I have for category 1, for example:

Customer A - 10

Customer B - 25

Customer C - 15

I'd like to be able to see
Customer B - 100%
Customer C - 60%
Customer A - 40%

Is it possible?

[Ashish_Mathur]

You are welcome.  I do not understand your requirement.

[smpa01]

@diegomenguecreate 3 measures

sales := SUMX(sales,sales[Quantity])
running total:= 
VAR _1 = MAX(sales[Date])
VAR _2 = CALCULATE([sales],ALL(sales[Date]),sales[Date]<=_1)
RETURN _2
Rank:= RANKX(ALLSELECTED(sales[Customer]),[running total],,DESC)

and use them like following

[smpa01]

@diegomenguedid you try the above ?