Forum Discussion

cosminc's avatar
cosminc
Icon for Post Partisan rankPost Partisan
7 years ago
Solved

index dax

Hi

i have a table made on report page which is like Salesmen on descending order after Sales (on query editor is more vast the source of it)

and i need to put an index descending, same always

somehow when i filter period - year, month or couple of salesmen, or other filters i want to display them

more than that i want to have a filter range where i can select top 20 or 21-40 let's say

and see also how much represent on total, and sales variation than same period from previous year

 

is it posible?

it is better to not create an agregation source alternative because it will difficult to put in relationship with main source; i have many sheets and i want to have the same filters

 

Thanks,

Cosmin

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi cosminc 

    Create measures

    sales-measure =
CALCULATE (
    SUM ( Sheet7[Sales] ),
    FILTER ( ALLSELECTED ( Sheet7 ), [Salesman] = MAX ( Sheet7[Salesman] ) )
)


rankx = RANKX(ALLSELECTED(Sheet7),[sales-measure],,DESC,Dense)

     

    Best Reagrds

    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi cosminc 

    Would you like a dynamic index?

    When you filter the data, it shows some specific data on the table visual,

    Then the index would automatically show the correct order according to the values shown on the table currently.

    Right?

     

    If so, it is possible.

    You could refer to similar threads first.

    DAX Formula: Dynamic Index on Selection of slicer in Chart

    Change Rank Dynamically by user selected Filter

    Create Dynamic Index Column/Measure using power query

     

    If you have problem implementing for your scenario, please show some example data and desired output so i can work on your scenario.

     

    Best Regards

    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • cosminc's avatar
      cosminc
      Icon for Post Partisan rankPost Partisan
      7 years ago

      Hi Maggie, 

      thanks for you intervention

      the examples that you gave me are not very similar

       

      below is an ex like my initial base

      YearMonthNoSalesmanSales
      20192George59
      20191George68
      20181George71
      20182George94
      20183George89
      20184George83
      20185George53
      20186George91
      20187George83
      20188George51
      20189George78
      201810George64
      201811George88
      201812George99
      20171George97
      20172George93
      20173George100
      20174George82
      20175George92
      20176George65
      20177George90
      20178George52
      20179George99
      201710George80
      201711George67
      201712George95
      20191Alex59
      20181Alex86
      20182Alex50
      20183Alex65
      20184Alex79
      20185Alex63
      20186Alex94
      20187Alex97
      20188Alex96
      20189Alex98
      201810Alex97
      201811Alex80
      201812Alex92
      20171Alex87
      20172Alex79
      20173Alex57
      20174Alex61
      20175Alex88
      20176Alex77
      20177Alex62
      20178Alex78
      20179Alex60
      201710Alex55
      201711Alex63
      201712Alex57
      20182Teo88
      20183Teo56
      20184Teo85
      20185Teo80
      20186Teo86
      20187Teo73
      20188Teo51
      20189Teo67
      20192Jenna93
      20191Jenna63
      20181Jenna100
      20182Jenna78
      20183Jenna73
      20184Jenna73
      20185Jenna74
      20186Jenna58
      20187Jenna93
      20188Jenna97
      20189Jenna100
      201810Jenna59
      201811Jenna84
      201812Jenna78
      20171Jenna84
      20172Jenna66
      20173Aimee72
      20174Aimee77
      20175Aimee53
      20176Aimee98
      20177Aimee99
      20178Aimee98
      20179Aimee80
      201710Aimee60
      201711Aimee58
      201712Aimee92
      20191Aimee59
      20181Aimee86
      20182Aimee63
      20183Jason61
      20184Jason94
      20185Jason86
      20186Jason53
      20187Jason81
      20188Jason94
      20189Jason51
      201810Jason70
      201811Jason52
      201812Jason91
      20171Jason73
      20172Jason62
      20173Jason65
      20174Jason97
      20175Jason55
      20176Jason57
      20177Jason72
      20178Jason53
      20179Jason72
      201710Jason87
      201711Jason50
      201712Jason72
      20182Jason70
      20183Jason68
      20184Jason84
      20185Jason62
      20186Jason53
      20187Jason91
      20188Jason79
      20189Jason94

       

      and i want to obtain something like this 

       

      all need to be dinamically when i filter Year, Month, Salesman or other dimensions which i have on my real base

      it would very helpful your input, i'm stucked on this

      Thanks in advance,

      Cosmin

       

       

       

      • v-juanli-msft's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi cosminc 

        Create measures

        sales-measure =
CALCULATE (
    SUM ( Sheet7[Sales] ),
    FILTER ( ALLSELECTED ( Sheet7 ), [Salesman] = MAX ( Sheet7[Salesman] ) )
)


rankx = RANKX(ALLSELECTED(Sheet7),[sales-measure],,DESC,Dense)

         

        Best Reagrds

        Maggie

         

        Community Support Team _ Maggie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.