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

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

Phil_Seamark Super Contributor

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

AC1 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

AC1 Frequent Visitor

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

It worked!!

Thank you.

Cheers,

Alex

Announcements #### Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future. Learn the answers to some of the questions asked during the Amanda Triple A event. #### October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.  Top Kudoed Authors
Users Online
Currently online: 370 members 3,457 guests
Recent signins:
• ale_leme • maximus84 • KoenVdB • lucamilanesi • vamshik153 • Yogisha • HxH • pureillusion • SibaniS • AndrewKaddas • GG77 • msportbase • laszloprekop • skseiry 