cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AC1
Frequent Visitor

Max of most Extensive List of Group or User Filter

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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft Phil_Seamark
Microsoft

Re: Max of most Extensive List of Group or User Filter

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Moderator v-qiuyu-msft
Moderator

Re: Max of most Extensive List of Group or User Filter

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
AC1
Frequent Visitor

Re: Max of most Extensive List of Group or User Filter

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

 

 

Microsoft Phil_Seamark
Microsoft

Re: Max of most Extensive List of Group or User Filter

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

AC1
Frequent Visitor

Re: Max of most Extensive List of Group or User Filter

Hi @Phil_Seamark,

 

That looks good.

 

Will try it now and keep you posted.

 

Thank you.

 

Cheers,

Alex

AC1
Frequent Visitor

Re: Max of most Extensive List of Group or User Filter

Hi @Phil_Seamark,

 

It worked!!

 

Thank you.

 

Cheers,

Alex

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors