Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
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
Solved! Go to 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
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
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
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:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |