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.
Hi Everybody
I have created two Ranking measure ,
1) To give Monthly Avg Selling Price (ASP) Ranking by Seller (herein called as Arranger) either for Top 5, Top 10, Top 20 (Selection made through slicers)
2) To give YTD ASP Ranking by Seller (Similar to above)
These two measures are included in a final measure called "Highest ASP by Arranger" which will reflect month or YTD ranking based on the slicer selection .
The monthly ranking is working fine , howerver when I toggle to YTD, it does not . It just shows only 2 Arrangers( Salesperson) even though when Top 5 are selected. There are more than 2 Arrangers .
Below are the snapshot and the measures. Due to sensitivity of Data, I'm unable to share the actual PBIX.
I presume there must somethig going wrong while filtering the table when YTD calculations are worked out.
Need help to crack the issue.
Thanks
Krishna
Top Coffin ASP by Arranger by month =
Top Coffin ASP by Arranger by month = VAR RankingDimension = VALUES('Trans Data'[Arranger]) VAR RankingSelect = [Top Ranking Select] RETURN CALCULATE([Avg Selling Price (Billed) Monthly],FILTER(RankingDimension,RANKX(ALL('Trans Data'[Arranger]),[Avg Selling Price (Billed) Monthly],,DESC)<=RankingSelect)
Top Coffin ASP by Arranger YTD =
VAR RankingDimension = VALUES('Trans Data'[Arranger])
VAR RankingSelect = [Top Ranking Select]
RETURN CALCULATE([Avg Selling Price (Billed) YTD],FILTER(RankingDimension,RANKX(ALL('Trans Data'[Arranger]),[Avg Selling Price (Billed) YTD],,DESC,Dense)<=RankingSelect))
Highest Coffin ASP by Arranger = IF(HASONEVALUE(View[Cal ID]),SWITCH([Calculation ID],1,[Top Coffin ASP by Arranger by month],2,[Top Coffin ASP by Arranger YTD]))
Hi Krishna_Mysore,
Based on your description, [Avg Selling Price (Billed) Monthly] and [Avg Selling Price (Billed) YTD] are measures you have created, right? Are they based on a same column?
And also please check if there’s something wrong in measure [Avg Selling Price (Billed) YTD].
Regards,
Jimmy Tao
Hi Jimmy
On further digging I understand the measure is not displaying if an "Arrangerr" does not have Sales in the current month.
To illustrate this , below is the screen dump . "Rosemary Harris" the first record in the table has YTD sales but no sales in the month of March . The measure "Highest Coffin ASP by Arranger" does not display the Value though there are are YTD sales associated with this Arranger. You can evidence in the table, that she has Sales Value of $33789 and the Avg Selling Price is $3754.
Maybe the syntax relating to filter is not right.
Any help much appreciated?
This is overdue for me to reslove. Anybody out there who can help me?
Thanks
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |