cancel
Showing results for
Did you mean:
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

## 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```

Proud to be a Datanaut!

5 REPLIES 5
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
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

## 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```

Proud to be a Datanaut!

Frequent Visitor

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

That looks good.

Will try it now and keep you posted.

Thank you.

Cheers,

Alex

Frequent Visitor

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

It worked!!

Thank you.

Cheers,

Alex

Announcements

#### 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!

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

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