Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Top & Bottom measure filter in same visual

Hi All,

 

I am working on a measure I want to see my top & bottom in the same visual for that I have written the query mentioned below but when I get my bar graph visual and put the name on the X axis & sum total on the Y axis and put the measure in the filter visual and apply the filter as is not blank my bar graph gets blank. My name has duplicate entries I am unsure of what's affecting my visual can someone help please 

amitchandak requesting you to help

 

Top & Bottom Selection =
Var TopX = SELECTEDVALUE(Slicer[Value])
Var TopRank =
IF(HASONEVALUE(Name,RANKX(ALLSELECTED(Name),CALCULATE(SUM(totalamount)),,DESC))
Var BottomRank = IF(HASONEVALUE(Name,RANKX(ALLSELECTED(Name),CALCULATE(SUM(totalamount)),,ASC))
Var Result =
SWITCH(
    TRUE(),
    TopRank <= TopX,1,
    BottomRank <= TopX,-1,
    BLANK())
    return Result

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Anonymous , Try a measure like this with TOPN or window

     

    calculate(SUM(totalamount), KEEPFILTERS(SELECTEDVALUE(Slicer[Value]), ALLSELECTED(Table[Name]), CALCULATE(SUM(totalamount)),desc))
    + calculate(SUM(totalamount), KEEPFILTERS(SELECTEDVALUE(Slicer[Value]), ALLSELECTED(Table[Name]), CALCULATE(SUM(totalamount)),asc))

     

     

    Just add top and bottom measure

     

    Learn Power BI: Dynamic TOPN using TOPN/Window and Numeric parameter: https://youtu.be/vm2mdEioQPQ

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      I am sorry I didn't understand do I replace this with the code that I have or add this in the code and do I apply anything on the visual filter ?