Max of most Extensive List of Group or User Filter

[AC1]

Hi,

This request is similar to: https://community.powerbi.com/t5/Desktop/DAX-Calculate-the-Max-for-a-Group/m-p/64645/highlight/true#..., though I struggled to get the solution to work for me.

I was hoping someone could help me create a measure (let's call this measure [MS]) that maximise [Searches] by [Date], [Size] & user filter (if any filter is applied). I will then divide the sum of [Orders] with the sum of [MS] to get conversion. The examples below hopefully will make it more clear what I am after.

Example:

Date	Size	Brand	Product	Searches	Orders
01/01/2017	X	A	110	250	2
01/01/2017	X	A	111	250	9
01/01/2017	X	B	120	250	9
01/01/2017	Y	A	210	150	8
01/01/2017	Y	B	220	150	5
01/01/2017	Y	C	230	150	9
01/01/2017	Y	C	230	150	8
01/01/2017	Z	B	320	500	2
02/01/2017	X	A	111	350	2
02/01/2017	X	B	120	350	7
02/01/2017	X	B	120	350	7
02/01/2017	Y	B	121	150	3
02/01/2017	Y	C	231	150	8
02/01/2017	Z	A	310	450	3
02/01/2017	Z	B	320	450	6
02/01/2017	Z	B	320	450	1
02/01/2017	Z	B	321	450	7
02/01/2017	Z	C	330	450	7

1. If no filter is applied: Conversion = SUM([Orders]) / SUM([MS]) = 103 / (250 + 150 + 500 + 350 + 150 + 450) = 5.57%
2. If Size = X & Brand = A: Conversion = SUM([Orders]) / SUM([MS]) = (2+9+2) / (250 + 350) = 2.17%
3. If Size = X & Brand = A & Product = 111: Conversion = SUM([Orders]) / SUM([MS]) = (9+2) / (250 + 350) = 1.83%
4. If Brand = A: Conversion = SUM([Orders]) / SUM([MS]) = 24 / (250 + 150 + 350 + 450) = 2.00%
5. If Date = 01/01/217 & Brand = A: Conversion = SUM([Orders]) / SUM([MS]) = (2 + 9 + 😎 / (250 + 150) = 4.75%
6. By date, Brand = A: {01/01/2017} Conversion = SUM([Orders]) / SUM([MS]) = (2 + 9 + 😎 / (250 + 150) = 4.75% & {02/01/2017} Conversion = SUM([Orders]) / SUM([MS]) = (2 + 3) / (350 + 450) = 0.63%

Note, the last example shows how we might have the conversion shown in a line chart, with date on the x-axis. Similar the data might be split by any of the columns above, hence [MS] will need to show a figure for each of the values in that column. We might also have the user select 2 sizes, brands, etc., hence [MS] need to combine the searches appropriately for both.

I would prefer the solution works in DirectQuery, but I will be happy with any solution.

There might be a better way of doing this and I am happy to hear alternative solutions, as long as it achieves the results in the example.

I hope this makes sense, else please just write and I will try and explain it better.

Cheers,

Alexander

[Phil_Seamark · Accepted solution]

hi @AC1

How does this look?  Just replace where I have Table6 with your tablename.

MS = 
VAR 
	P = SUMX(SUMMARIZE(Table6,'Table6'[Date],'Table6'[Size],"Searches",MAX('Table6'[Searches])),[Searches])
VAR T =  DIVIDE(
			CALCULATE(
				SUM(Table6[Orders])
				),
			P)
				
RETURN T

[AC1]

Hi @Phil_Seamark,

It worked!!

Thank you.

Cheers,

Alex

[AC1]

Hi @Phil_Seamark,

That looks good.

Will try it now and keep you posted.

Thank you.

Cheers,

Alex

[v-qiuyu-msft]

Hi @AC1,

From "create a measure (let's call this measure [MS]) that maximise [Searches] by either group ([Date], [Size]) or user filter (whichever has the most rows", the measure "MS" will return the maximum [Searches] based on the filters. For example, if Size = X & Brand = A: Conversion = SUM([Orders]) / SUM([MS]) = (2+9+2) / (250 + 350) = 2.17%, the [MS] and Sum([MS]) return 350. Why in your scenario, it's (250+350)?

Also for your 5 conditions, each will return one value, why to you want to display results in a line chart instead of the card visual.

Best Regards,
Qiuyun Yu

[AC1]

Hi,

Thanks for looking at my query.

In regards to "Also for your 5 conditions, each will return one value, why to you want to display results in a line chart instead of the card visual." the line chart is when we will find the [MS] by each date, but maybe over several sizes, brands and products. I realise the examples does not show that, hence I wrote "Note, we might have the conversion shown in a line chart, with date on the x-axis. " afterwards... 🙂 I'll add an example now:

1. Date on x-axis, Brand = A: {01/01/2017} Conversion = SUM([Orders]) / SUM([MS]) = (2 + 9 + 😎 / (250 + 150) = 4.75% & {02/01/2017} Conversion = SUM([Orders]) / SUM([MS]) = (2 + 3) / (350 + 450) = 0.63%

The reason you are confused with “Why in your scenario, it's (250+350)?” is because I haven’t explained the way [MS] works accurately. [MS] is the maximum of [Searches] by [Date] & [Size] & user filter (if any). So in this example we use [Date] & [Size] & [Brand] and get 250 as max of {01/01/2017}, {X}, {A} and 350 as max of {02/01/2017}, {X}, {A}. I will change the explanation in my original post.

Thanks again.

Cheers,

Alex