cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MohanV
Helper III
Helper III

Filter out Ticket Sales Ranks by MAX of Net Revenue

Hello All,

 

Sample Data:

MohanV_0-1604312645329.png

 

 

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
)

 

 

 

MohanV_2-1604313243779.png

 

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.

MohanV_3-1604313589949.png

 

Any help please.

Thanks,

Mohan V.

1 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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

5 REPLIES 5
v-zhenbw-msft
Community Support
Community Support

Hi @MohanV ,

 

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]))

 

Fi1.jpg

 

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
Super User
Super User

@MohanV 

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
)

Fowmy_0-1604316384703.png

 

________________________

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

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


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 🙂

YouTube Linkedin

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

harshnathani
Super User
Super User

Hi @MohanV ,

 

You can try this.

 

Create a column to rank the segments

 

1.jpg

 

 

Ranking Segment = RANKX(FILTER('Table','Table'[Ticket Sales] = EARLIER('Table'[Ticket Sales])),'Table'[Net Revenue])

 

2.JPG

 

 

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)

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!