DAX support

Question

&nbsp;Hi,&nbsp;here is my data sample.https://www.dropbox.com/s/1n3gtyqxb8wjncn/data.xlsx?dl=0&nbsp;what i would like to achieve is,Top Core 10 core account based on&nbsp;TER_FYTD_USDNext 15 core account based on&nbsp;TER_FYTD_USDRemainder Core account based on&nbsp;TER_FYTD_USD&nbsp;This is my table in Power BI.Account&nbsp; &nbsp; &nbsp; &nbsp; AccountSegment&nbsp; &nbsp; TER_FYTD_USDEigCore0DolCore33914.75818Core0818Core-7439.3DE&nbsp;G3600DE&nbsp;G3600DE&nbsp;Core0DE&nbsp;Core0DE&nbsp;Core0DE&nbsp;Core0DE&nbsp;Core0DE&nbsp;Core0EdiCore0EdiCore31351.83162EdiCore0EdiCore52873.82172EdiCore0EdiCore0EdiCore493.367909

v-yuta-msft · Answer

Hi&nbsp;vjnvinod,
&nbsp;
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] &lt;= 10 &amp;&amp; Table1[AccountSegment] = "Core"), "No" &amp; [Rank] &amp; ":" &amp; [Account], ", ", [Rank], ASC)
Next 15 core account = CONCATENATEX(FILTER(Table1, Table1[Rank] &gt; 10 &amp;&amp; Table1[Rank] &lt;= 25 &amp;&amp; Table1[AccountSegment] = "Core"), "No" &amp; [Rank] &amp; ":" &amp; [Account], ", ", [Rank], ASC)
Remainder Core account = CONCATENATEX(FILTER(Table1, Table1[Rank] &gt; 25 &amp;&amp; Table1[AccountSegment] = "Core"), "No" &amp; [Rank] &amp; ":" &amp; [Account], ", ", [Rank], ASC)
The result is as below:
&nbsp;
&nbsp;
Hope it's helpful to you.
&nbsp;
Jimmy Tao

vjnvinod · Answer

v-yuta-msft&nbsp;This is super helpful, but i think this is not what i want to achieve but a visual something like below&nbsp;is this view possible?&nbsp;1) Account having G360 as a Account segment should remain as it is2) Filter out Top 10 core accounts (by summing up the&nbsp;similar accounts(using TER_FYTD_USD value) which is shown as in multiple lines)3) similarly for Next 15 Core accounts4) Similarly for Remainder accounts&nbsp;

v-yuta-msft · Answer

Hi&nbsp;vjnvinod,
&nbsp;
You said "by summing up the&nbsp;similar accounts", "Similarly". Could you clarify your logic of how to achieve them and more details about your requirement?
&nbsp;
Regards,
Jimmy Tao

vjnvinod · Answer

v-yuta-msft&nbsp;my requirement is to get the below view, is it possible? this is basically a waterfall chart in PPTNow 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&nbsp;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.&nbsp;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 created1) if accounts are G360, we don't have to rank it, keep the calculated coloumn as G360&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2) if an accounts are Core, Club the similar accounts&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;( eg ABC account&nbsp; &nbsp; core segment,&nbsp; &nbsp;TER 2000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ABC account&nbsp; &nbsp; core segment,&nbsp; &nbsp;TER&nbsp; 2500&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Output&nbsp; ABC account&nbsp; &nbsp;&nbsp;core segment&nbsp; &nbsp; &nbsp;TER 4500then rank it &amp;&nbsp; Identiy those top 10 core accounts based on TER_FYTD values &amp; keep the&nbsp;calculated coloumn as&nbsp;TOP 10 Core account&nbsp;3)&nbsp;&nbsp;if accounts are Core, Club the account&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;( eg ABC account&nbsp; &nbsp; core segment,&nbsp; &nbsp;TER 2000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ABC account&nbsp; &nbsp; core segment,&nbsp; &nbsp;TER&nbsp; 2500&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Output&nbsp; ABC account&nbsp; &nbsp;&nbsp;core segment&nbsp; &nbsp; &nbsp;TER 4500&nbsp;then rank it &amp;&nbsp; Identiy those&nbsp;Next 15 core accounts based on TER_FYTD values &amp;&nbsp;keep the&nbsp;calculated coloumn as&nbsp;Next&nbsp;15 Core account&nbsp;4)&nbsp;&nbsp;if an account is Core, Club the account&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;( eg ABC account&nbsp; &nbsp; core segment,&nbsp; &nbsp;TER 2000&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ABC account&nbsp; &nbsp; core segment,&nbsp; &nbsp;TER&nbsp; 2500&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Output&nbsp; ABC account&nbsp; &nbsp;&nbsp;core segment&nbsp; &nbsp; &nbsp;TER 4500&nbsp;then rank it &amp;&nbsp; Identiy those&nbsp;Remainder&nbsp;core accounts based on TER_FYTD values &amp;&nbsp;keep the&nbsp;calculated coloumn as&nbsp;Remainder Core accountis it possible to create a caculated coloumn to achieve the below view?&nbsp;

vjnvinod · Answer

v-yuta-msft&nbsp;let me know if this is something we can do in Power BI

Forum Discussion

DAX support

5 Replies

Related content

dax support - summ of grades

DAx Support on cartesian table

DAX Support

DAX/Measure Support

Campaign Voucher FAQ & Support

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