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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

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!