DAX support

[vjnvinod]

Hi,

here is my data sample.

https://www.dropbox.com/s/1n3gtyqxb8wjncn/data.xlsx?dl=0

what i would like to achieve is,

Top Core 10 core account based on TER_FYTD_USD

Next 15 core account based on TER_FYTD_USD

Remainder Core account based on TER_FYTD_USD

This is my table in Power BI.

Account        AccountSegment    TER_FYTD_USD

Eig	Core	0
Dol	Core	33914.75
818	Core	0
818	Core	-7439.3
DE	G360	0
DE	G360	0
DE	Core	0
DE	Core	0
DE	Core	0
DE	Core	0
DE	Core	0
DE	Core	0
Edi	Core	0
Edi	Core	31351.83162
Edi	Core	0
Edi	Core	52873.82172
Edi	Core	0
Edi	Core	0
Edi	Core	493.367909

[v-yuta-msft]

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

[vjnvinod]

@v-yuta-msft

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

[v-yuta-msft]

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

[vjnvinod]

@v-yuta-msft

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?

[vjnvinod]

@v-yuta-msft

let me know if this is something we can do in Power BI