Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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.
Dear All,
Thx for the outstanding support. problem has been fixed.
Regards,
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
@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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |