Forum Discussion

FarsH's avatar
FarsH
New Member
3 years ago

How to Identify top Performing based on selected parameter

hi, I'm working on a report and I want to give flexibilty to the user such that each time the user select a certain product from the slicer "Products", and chooses a desired parameter from the "Metr...
Interesting Links
Need Help
Tips and Tricks
Sahir_Maharaj's avatar
Sahir_Maharaj
Icon for Super User rankSuper User
3 years ago

Hello FarsH,

 

To generate the desired ranking, you can modify your approach and use the SWITCH function instead of IF statements to handle multiple conditions.

 

Could you please try this:

 

TopT =
VAR SelectedProduct = SELECTEDVALUE('Values'[Product SlicerP Order])
VAR SelectedMetric = SELECTEDVALUE('Values'[Metrics Value])
RETURN
SWITCH(
    SelectedMetric,
    "Net Sales", RANKX(FILTER(ALLSELECTED('Mediterranean Cameras Company'[Country], 'Mediterranean Cameras Company'[BuyerType], 'Mediterranean Cameras Company'[Month], 'Mediterranean Cameras Company'[Day]), 'Mediterranean Cameras Company'[Product] = SelectedProduct), [Net Sales],,DESC) <= 1,
    "Net Profit", RANKX(FILTER(ALLSELECTED('Mediterranean Cameras Company'[Country], 'Mediterranean Cameras Company'[BuyerType], 'Mediterranean Cameras Company'[Month], 'Mediterranean Cameras Company'[Day]), 'Mediterranean Cameras Company'[Product] = SelectedProduct), [Net Profit],,DESC) <= 1,
    "Net Profit Margin", RANKX(FILTER(ALLSELECTED('Mediterranean Cameras Company'[Country], 'Mediterranean Cameras Company'[BuyerType], 'Mediterranean Cameras Company'[Month], 'Mediterranean Cameras Company'[Day]), 'Mediterranean Cameras Company'[Product] = SelectedProduct), [Net Profit Margin],,DESC) <= 1,
    "Total Unit Sold", RANKX(FILTER(ALLSELECTED('Mediterranean Cameras Company'[Country], 'Mediterranean Cameras Company'[BuyerType], 'Mediterranean Cameras Company'[Month], 'Mediterranean Cameras Company'[Day]), 'Mediterranean Cameras Company'[Product] = SelectedProduct), [Total Unit Sold],,DESC) <= 1,
    BLANK()
)

 

The SWITCH function checks the selected metric value and uses the appropriate RANKX function to generate the ranking of top/bottom values based on the selected product.

 

The RANKX function filters the table based on the selected product and then calculates the ranking of the values based on the selected metric.

 

Let me know if you might require further assistance.