index dax

[cosminc]

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

[v-juanli-msft]

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]

Hi Maggie, 

thanks for you intervention

the examples that you gave me are not very similar

below is an ex like my initial base

Year	MonthNo	Salesman	Sales
2019	2	George	59
2019	1	George	68
2018	1	George	71
2018	2	George	94
2018	3	George	89
2018	4	George	83
2018	5	George	53
2018	6	George	91
2018	7	George	83
2018	8	George	51
2018	9	George	78
2018	10	George	64
2018	11	George	88
2018	12	George	99
2017	1	George	97
2017	2	George	93
2017	3	George	100
2017	4	George	82
2017	5	George	92
2017	6	George	65
2017	7	George	90
2017	8	George	52
2017	9	George	99
2017	10	George	80
2017	11	George	67
2017	12	George	95
2019	1	Alex	59
2018	1	Alex	86
2018	2	Alex	50
2018	3	Alex	65
2018	4	Alex	79
2018	5	Alex	63
2018	6	Alex	94
2018	7	Alex	97
2018	8	Alex	96
2018	9	Alex	98
2018	10	Alex	97
2018	11	Alex	80
2018	12	Alex	92
2017	1	Alex	87
2017	2	Alex	79
2017	3	Alex	57
2017	4	Alex	61
2017	5	Alex	88
2017	6	Alex	77
2017	7	Alex	62
2017	8	Alex	78
2017	9	Alex	60
2017	10	Alex	55
2017	11	Alex	63
2017	12	Alex	57
2018	2	Teo	88
2018	3	Teo	56
2018	4	Teo	85
2018	5	Teo	80
2018	6	Teo	86
2018	7	Teo	73
2018	8	Teo	51
2018	9	Teo	67
2019	2	Jenna	93
2019	1	Jenna	63
2018	1	Jenna	100
2018	2	Jenna	78
2018	3	Jenna	73
2018	4	Jenna	73
2018	5	Jenna	74
2018	6	Jenna	58
2018	7	Jenna	93
2018	8	Jenna	97
2018	9	Jenna	100
2018	10	Jenna	59
2018	11	Jenna	84
2018	12	Jenna	78
2017	1	Jenna	84
2017	2	Jenna	66
2017	3	Aimee	72
2017	4	Aimee	77
2017	5	Aimee	53
2017	6	Aimee	98
2017	7	Aimee	99
2017	8	Aimee	98
2017	9	Aimee	80
2017	10	Aimee	60
2017	11	Aimee	58
2017	12	Aimee	92
2019	1	Aimee	59
2018	1	Aimee	86
2018	2	Aimee	63
2018	3	Jason	61
2018	4	Jason	94
2018	5	Jason	86
2018	6	Jason	53
2018	7	Jason	81
2018	8	Jason	94
2018	9	Jason	51
2018	10	Jason	70
2018	11	Jason	52
2018	12	Jason	91
2017	1	Jason	73
2017	2	Jason	62
2017	3	Jason	65
2017	4	Jason	97
2017	5	Jason	55
2017	6	Jason	57
2017	7	Jason	72
2017	8	Jason	53
2017	9	Jason	72
2017	10	Jason	87
2017	11	Jason	50
2017	12	Jason	72
2018	2	Jason	70
2018	3	Jason	68
2018	4	Jason	84
2018	5	Jason	62
2018	6	Jason	53
2018	7	Jason	91
2018	8	Jason	79
2018	9	Jason	94

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 · Accepted solution]

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.

[cosminc]

i played a little further and i have some issues

do you know why the total is not right for the sales measure?

i struggle with 2 situation:

- i made a table and when select a rank range - 20-60 or top 20 i obtain the sales for each, less the rank out of the range

- in a card i want to put this measure and see the total - for top 20, other card for top21-60

any idea?

thanks!

Cosmin

[v-juanli-msft]

Hi @cosminc 

To get the proper total 

replace the sales measure as follows

sales-measure =
VAR m =
    CALCULATE (
        SUM ( Sheet7[Sales] ),
        FILTER ( ALLSELECTED ( Sheet7 ), [Salesman] = MAX ( Sheet7[Salesman] ) )
    )
RETURN
    IF (
        HASONEVALUE ( Sheet7[Salesman] ),
        m,
        CALCULATE ( SUM ( Sheet7[Sales] ), ALLSELECTED ( Sheet7 ) )
    )

situtation1

create a new table by entering data

Top N	max number	min number
2	2	1
2~4	4	3
others

don't connect this table to any other tables

cretae measures in the main table

selected min = SELECTEDVALUE(Table1[min number])

selected max = SELECTEDVALUE(Table1[max number])

flag = IF([rankx]<=[selected max]&&[rankx]>=[selected min],1,0)

situtation2

create a measure

total_in_card = CALCULATE([sales-measure],FILTER(Sheet7,[flag]=1))

Add the  measure above in a card visual, it will show the proper total

Open my pbix, look at the Page2.

Best Regards

Maggie

[cosminc]

Hi Maggie,

i have troubles with this measure

total_in_card = CALCULATE([sales-measure],FILTER(Sheet7,[flag]=1))

on a small data base all it works great; the issue is that i have a base with thousands of rows 😞

for the table it takes 15-20 sec to display and for card it seems it doesn't work

on a first view i suppose that flag filter takes huge memory 

and i need to create couple of cards (top 20, top 21-60 and others and % in total for each and % comparison with same period last year)

if you have time please can you try to develop the solution starting from this point? (i made a new post few days ago before you had given  to me this solution)

https://community.powerbi.com/t5/Desktop/Incorrect-Measure-Total-table-with-index-selection-variable...

it seems that if you do't put flag filter and instead you put 0 where the salesmen should be excluded works more rapidly with less memory consuming

Thank for your involvement!

Cosmin

[cosminc]

also for the similar table that you put in your pbix i need to add a column with Sales for previous year and other column with the evolution between them (and the total evolution)

Thanks,

Cosmin

[cosminc]

Hi

thanks, it works fine

i have a big base and it takes 15 seconds to view the table, but works fine.

i think i miss somethig because if i use filters year and month from the source it works, but i use a calendar in relationship with the base and other sources 

and it causes some troubles

may you try with your base, maybe you see a solution; i have to use the calendar 

thanks again for your help

Cosmin