Filter out Ticket Sales Ranks by MAX of Net Revenue

[Anonymous]

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.

[v-zhenbw-msft]

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.

[Fowmy]

@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 🙂

YouTube  LinkedIn

[Anonymous]

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

[Fowmy · Accepted 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 🙂

YouTube Linkedin

[harshnathani]

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)