Max Function

[Anonymous]

Dear Experts,

i have a table contain lead solds by quarter and by channel as per the below:

Area     lead sold     Quarter

A                 50                  Qtr 1

B                 75                   Qtr1

C                100                 Qtr1

A                200                 Qtr2

B                 75                   Qtr2

C                 50                   Qtr2

 and so on till Qtr 4

what im trying to do since last week and it didnt work i want to display the max lead sold per quarter and area as an example:

is it possibe to have the below output:

Quarter    lead sold     area

Qtr 1             100                C

Qtr2               200               A

and so on to Q3 and Q4

Please advise as nothing that i tried worked with me.

Thanks in advance,

N

[amitchandak · Accepted solution]

Create a Rank and filter Rank 1.

Refer

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...


https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[az38 · Accepted solution]

Sorry, @Anonymous 

my bad. try Rank by SUM

Rank = rankx(filter('Table1';Table1[YearQuarter]=earlier('Table1'[YearQuarter]));sum(Table1[lead sold]))

 also, you could share your pbix-file on any cloud service like https://uploadfiles.io/

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Dear All,

Thx for the outstanding support. problem has been fixed.

Regards,

[v-joesh-msft · Accepted solution]

Hi @Anonymous ,

You can try the following methods:
1. Create a calculated column as follows:

Quarter = "Q" & INT ( FORMAT ( [Date], "q") )

2. Create the following two measures:

sum_LS = sum('Table'[Leads_Sold])

Rank = RANKX(ALL('Table'[AREA]),[sum_LS])

3. Filter Rank:

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ERi36XK2deZJqSrulxj0YjgBr_6f9YvEk0s6U8GYlOJvtg?e=lkjgaE

Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[az38]

Hi @Anonymous 

first, you can add a new column rank

Rank = rankx(filter('Table1';Table1[Quarter]=earlier('Table1'[Quarter]));Table1[lead sold]) 

then either filter only values with rank=1 in your visual or create calculated table:

FilteredTable = filter('Table1';Table1[Rank]=1)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[Anonymous]

Dear Sir, @az38 

Appologies as i might have populated data already sumarized and that why probably your function didnt work with me, the real data look like the attached file.

Please let me know if you required any other clarifications.

Thanks in advance

N

[az38]

@Anonymous

i woul recommend you to:

1. add a calculated column in your data table 

YearQuarter = FORMAT([Date];"YYYY q")

2. add a rank column to your data table. it will rank your sold by quarter independ on areas

Rank = rankx(filter('Table1';Table1[YearQuarter]=earlier('Table1'[YearQuarter]));Table1[lead sold])

then as I told you above either filter only values with rank=1 in your visual or create calculated table:

FilteredTable = filter('Table1';Table1[Rank]=1)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[Anonymous]

Hi @az38 

First I really appreciate your support, but you have to excuse my limited knowledge on power bi.

i have followed exactly the steps that you mentioned above and still didnt work i have attached a screen shot to show you how the data is being pouplated after creating the rank dax

please check image for your referenceRegards,

N

[az38 · Accepted solution]

Sorry, @Anonymous 

my bad. try Rank by SUM

Rank = rankx(filter('Table1';Table1[YearQuarter]=earlier('Table1'[YearQuarter]));sum(Table1[lead sold]))

 also, you could share your pbix-file on any cloud service like https://uploadfiles.io/

do not hesitate to give a kudo to useful posts and mark solutions as solution

[amitchandak · Accepted solution]

Create a Rank and filter Rank 1.

Refer

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...


https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601