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 Power BI Community,
I am new to DAX, and have trouble solving an issue even after reading posts about this matter.
I intend to Filter for the Products which rank 2nd based on volume.
After which, a chart needs to be displayed, showing the average of values.
From other posts, the solution seems to be a combination of RANKX and COUNTROWS.
However, I have not been able to obtain the correct result.
Attached are pictures of the dataset and report.
Dataset
Report, with instructions
Code which I used in Chart 1
Charges_AvgByCategory = CALCULATE(AVERAGEX(Info,Info[Charges]),ALLEXCEPT(Info,Info[Category]))
Thanks for reading my post, and thanks in advance for your help!!
Solved! Go to Solution.
Hi @Anonymous,
Column: When filtering for Provider (eg. Happy Hands), columns will automatically populate the 2nd highest volume Product (Palm Lotion, in this case), to obtain the average Charges of Palm Lotion, by Month
Please create below measures:
average = AVERAGE(Info[Charges]) rank = RANKX(ALL(Info[Product]),[average],,DESC,Dense)
In chart visual, add [Month] to axis, add [Product] to legend, and add measure [average] to value. Place [rank] to visual level filter and set its value to 2.
Line: When the above 2nd highest volume Product is populated, line will display a flat line for average of all Charges for this Product, regardless of Provider and Month
I'm afraid this is not achievable. As when you filter dataset by provider "Happy Hands", the records are restricted those highlighted in red. It is not possible to take the blue parts into account to calculate average.
Best regards,
Yuliana Gu
Hi @Anonymous,
Column: When filtering for Provider (eg. Happy Hands), columns will automatically populate the 2nd highest volume Product (Palm Lotion, in this case), to obtain the average Charges of Palm Lotion, by Month
Please create below measures:
average = AVERAGE(Info[Charges]) rank = RANKX(ALL(Info[Product]),[average],,DESC,Dense)
In chart visual, add [Month] to axis, add [Product] to legend, and add measure [average] to value. Place [rank] to visual level filter and set its value to 2.
Line: When the above 2nd highest volume Product is populated, line will display a flat line for average of all Charges for this Product, regardless of Provider and Month
I'm afraid this is not achievable. As when you filter dataset by provider "Happy Hands", the records are restricted those highlighted in red. It is not possible to take the blue parts into account to calculate average.
Best regards,
Yuliana Gu
Hi Yuliana,
Thanks for the solution to the first question, and for explaining the limitations that will occur for the second question!
It was a great help!
Regards,
StratVar
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |