Forum Discussion
Max Function
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
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-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601Sorry, 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
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:
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.
8 Replies
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:
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.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
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
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-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601