Filter for 2nd highest volume product, and display chart

[Anonymous]

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!!

[v-yulgu-msft · Accepted 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

[Anonymous]

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

[TomMartens]

Hey,

please share your pbix file. Upload the file to onedrive and dropbox and share the link.

Regards,
Tom

[Anonymous]

Thanks for the suggestion, Tom. Here is the URL for the .pbix file: https://drive.google.com/open?id=1gzmFCp30abJt5DOwCVnap2t7QBBQ21_C