Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello All,
Sample Data:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MACSpkAyVidayQnINgKLmENFnOEillARFyDbGCxiYQ4RcUWIGENE3OAi5hZAkVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Segment = _t, #"Ticket Sales" = _t, #"Net Revenue" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Segment", type text}, {"Ticket Sales", Int64.Type}, {"Net Revenue", Int64.Type}})
in
#"Changed Type"
I am trying to calculate the ranks of Ticket Sales and filter out the rows as,
Ranks =
RANKX (
CALCULATETABLE (
VALUES('Table'[Segment]),
FILTER(ALLSELECTED('Table'[Segment]),NOT(ISBLANK([_Sales])))
),
[_Sales],,DESC,Dense
)
With above dax i am able to get the ranks of Ticket Sales by Segment,
But if you see, Segment D, E, F having rank 1 with 300 Ticket Sales and B, C having rank 2 with 200 Ticket Sales.
Now when ever one rank is allocated to mulitple segments with same ticket sales, there, i need to consider the Net Revenue which is having max value for those.
for above case,
i need to consider only D Segment with 300 Ticket Sales, because, out of three segments (D,E,F) with 300 ticket sales, when we consider Net Revenue for these segments, D is only the max we need to consider.
Expected output.
Any help please.
Thanks,
Mohan V.
Solved! Go to Solution.
@MohanV
You can group your sales by static or dynamic buckets and find the maximum net revenue in each bucket and pick the segment.
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Anonymous ,
You can try to create a calculate table firstly using SUMMARIZE function. Then you can use the method that @Fowmy suggested.
Table = SUMMARIZE(Query1,Query1[Segment],"SUM1",SUM(Query1[Ticket Sales]),"SUM2",SUM(Query1[Net Revenue]))
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous
I created an independent new measure which you can assign to the table visual filter and set it to 1
Max Seg =
IF(
MAXX(
FILTER( ALL('Table'), 'Table'[Ticket Sales] = SELECTEDVALUE('Table'[Ticket Sales])),
'Table'[Net Revenue]
) = SELECTEDVALUE('Table'[Net Revenue]),
1,
0
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy, @harshnathani thanks for quick responce,
Much appreciated.
i guess i took you in a wrong direction.sorry for that.
Thing is, these Ticket Sales and Net Revenue are not the direct columns or direct measures (Ex: SUM(Table[Ticket Sales])).
Ticket Sales is actually a measure coming from Sales Table after doing some business required calculation.
Net Revenue is also a measure coming from Revenue Table after doing some business required calculation.
These both tables are joined with Segment table then the above data got generated.
So with this, i guess, it may not be possible to create a calculated column or i could do select the measure using MAXX as
@Fowmyyou suggested.
Apologies, for giving the clear information abou the same.
Thanks,
Mohan V.
@MohanV
You can group your sales by static or dynamic buckets and find the maximum net revenue in each bucket and pick the segment.
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Anonymous ,
You can try this.
Create a column to rank the segments
Ranking Segment = RANKX(FILTER('Table','Table'[Ticket Sales] = EARLIER('Table'[Ticket Sales])),'Table'[Net Revenue])
Filter the visualization wherever rank is 1.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |