Forum Discussion
DAX support
Hi vjnvinod,
To achieve your requirement, you should create a rank column first as below:
Rank = RANKX(FILTER(Table1, Table1[AccountSegment] = EARLIER(Table1[AccountSegment])), RANKX(ALL(Table1), Table1[TER_FYTD_USD]), , ASC, Dense)
Then create three measures two achieve your result:
Top Core 10 core account = CONCATENATEX(FILTER(Table1, Table1[Rank] <= 10 && Table1[AccountSegment] = "Core"), "No" & [Rank] & ":" & [Account], ", ", [Rank], ASC)
Next 15 core account = CONCATENATEX(FILTER(Table1, Table1[Rank] > 10 && Table1[Rank] <= 25 && Table1[AccountSegment] = "Core"), "No" & [Rank] & ":" & [Account], ", ", [Rank], ASC)
Remainder Core account = CONCATENATEX(FILTER(Table1, Table1[Rank] > 25 && Table1[AccountSegment] = "Core"), "No" & [Rank] & ":" & [Account], ", ", [Rank], ASC)
The result is as below:
Hope it's helpful to you.
Jimmy Tao
This is super helpful, but i think this is not what i want to achieve but a visual something like below
is this view possible?
1) Account having G360 as a Account segment should remain as it is
2) Filter out Top 10 core accounts (by summing up the similar accounts(using TER_FYTD_USD value) which is shown as in multiple lines)
3) similarly for Next 15 Core accounts
4) Similarly for Remainder accounts
Hi vjnvinod,
You said "by summing up the similar accounts", "Similarly". Could you clarify your logic of how to achieve them and more details about your requirement?
Regards,
Jimmy Tao
my requirement is to get the below view, is it possible? this is basically a waterfall chart in PPT
Now coming back to your question.
the ranking measures you have shared is actually not grouping the similar accounts into one and ranking it.
it means there are multiple lines for an account with TER_FYTD_USD values, apparently this multiple lines should club and become one single account and TER_FYTD_USD Value, eventually it should rank accordingly. In simple words PIVOT of the data which combines muliple lines into one.
My opinion is that the below view can be achieved only if we create a calculate coloumn instead of measure.
this is how calculated coloumn should be created
1) if accounts are G360, we don't have to rank it, keep the calculated coloumn as G360
2) if an accounts are Core, Club the similar accounts ( eg ABC account core segment, TER 2000
ABC account core segment, TER 2500
Output ABC account core segment TER 4500
then rank it & Identiy those top 10 core accounts based on TER_FYTD values & keep the calculated coloumn as TOP 10 Core account
3) if accounts are Core, Club the account ( eg ABC account core segment, TER 2000
ABC account core segment, TER 2500
Output ABC account core segment TER 4500
then rank it & Identiy those Next 15 core accounts based on TER_FYTD values & keep the calculated coloumn as Next 15 Core account
4) if an account is Core, Club the account ( eg ABC account core segment, TER 2000
ABC account core segment, TER 2500
Output ABC account core segment TER 4500
then rank it & Identiy those Remainder core accounts based on TER_FYTD values & keep the calculated coloumn as Remainder Core account
is it possible to create a caculated coloumn to achieve the below view?
