Need help with RANKX and assigning variables based on min max dynamically

[astahir85]

Hi power users,

I have a set of data that I need some help with. Here is my case. I have a table as such

Part Number - Revenue - Margin - Qty

0100 - $55 - 30% - 2

0200 - $600 - 20% - 10

0300 - $25 - 70% - 5

0400 - $679 - 80% - 60

0500 - $750 - 10% - 100

I'd like to create a dynamic measure that help me assign a variable to anything over $50 revenue as A, anything between $25 - $50 as B and below $25 as C. The issue is, I need it to be dynamic because this table with the same part numbers maybe different for different stores. i.e. 0100 maybe $30 with 25% and 100 qty. in that case, 0100 should be B and A - based on how I filter the visual/page.

Can someone please help me with this?

[amitchandak · Accepted solution]

[astahir85]

Thanks Amit, I feel like such a noob

[astahir85]

Thanks Amit, one more question. How can I look at a donut chart to see what percentage is A, B or C in terms of percentage. I can't drag A/B/C measure to values/legend, can only add it to tooltips. Hence I can't see what percentage was A or B or C

[amitchandak]

Under format option -> Detail labels -> label Style

[astahir85]

Hi Amit

I am adding 'Part number' in legend, 'Revenue' in Values and 'ABC' in Tooltips (can't move it anywhere else). This is not allowing me to see what you have. 

[astahir85]

Hi Amit

Any idea how I can distinct count this measure?

[amitchandak]

Drag a field on the graph under Value. The down angle or right-click will give you count distinct options. This only for columns not for measures.

[astahir85]

So I am using this formula

AAA Qty =IF([Total Qty]>100, "A", IF([Total Qty]>50, "B", "C"))

and when I drag this measure to the page it goes under toolstips rather than axis

[amitchandak]

Are you not able to drag it into the axis box ?

[astahir85]

No, it doesn't allow me. Doesn't flag me either, but any other table I can move it just not this measure