Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Top/Bottom Measure giving wrong value

Hi Everyone,

 

I have a measure to calculate Top/Bottom/All as follows :

Top/Bottom  =
VAR NValue =
    SELECTEDVALUE ( 'Drop Down '[Values] )
VAR Ranking_TOP_CUST =
    RANKX (
        ALLSELECTED ( 'Sales'[Product Group] ),
        [Current Period Revenue],
        ,
        DESC
    )
VAR Ranking_Bottom_CUST =
    RANKX (
        ALLSELECTED ( 'Sales'[Product Group] ),
        [Current Period Revenue],
        ,
        ASC
    )
VAR Result =
    SWITCH (
        SELECTEDVALUE ( 'Top/Bottom'[Selection] ),
        "Top", INT ( Ranking_TOP_CUST <= NValue ),
        "Bottom", INT ( Ranking_Bottom_CUST <= NValue ),
        "All", 1
    )
RETURN
    Result
 
 
Top/Bottom[Selection] column has values : Top,Bottom,All
Drop Down[Values] column has values : 2 to 30
 
I have then pulled this measure into my table as a visual level filter and set it as : Show Item when Value is 1
 
Now, I have a Year slicer and a Previous Period Revenue measure. When I apply the Top/Bottom Measure ,and select a Year the Previous Period Revenue is showing minor difference than it should . 
 
Previous Period Revenue =
VAR YTD_ = CALCULATE([Sales Out DMS],INIT_RAW_SG_VN_CDS[FIS_YR]=max(INIT_RAW_SG_VN_CDS[FIS_YR])-1,ALL(INIT_RAW_SG_VN_CDS[FIS_YR],INIT_RAW_SG_VN_CDS[FIS_QTR],INIT_RAW_SG_VN_CDS[FIS_MTH],INIT_RAW_SG_VN_CDS[FIS_WK]))+0
 
I am not able to identify what is wrong.. Would appreciate help.. 

 

Thanks in advance...

Swathi

 

tamerj1 amitchandak 

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Greg_Deckler 

       

      My requirement is as follows:

       

      i have a table visual which has Account Name, current period revenue and previous period revenue as values. 
      I need to give 2 slicers .

      1) to select Top/Bottom/All

      2) to select number between 2 and 30

       

      Now based on the slicer selections, my table visual should show change.

      eg: if I choose All then it should show all Account Name.

      if Top and 5 is selected, then table should show top 5 accounts with highest current period revenue. And similar for Bottom as well.