Top 2 tasks by month

[tulsi]

Hi,

I have to get top 2 tasks by month starting from august 2017-now. When I'm using the top n filter it's giving me common top 2 tasks for all the months. I need top 2 tasks for that particular month.

Data source looks something like this:

[themistoklis]

@tulsi

First create the measure based on your required field:

measure_new = CALCULATE(SUM(TableName[Amount]),ALLEXCEPT(TableName, TableName[ID]))

Then create another measurethat will be used in the filtering section (Top N lest say countries)

RANK=RANKX(ALLSELECTED(TableName),[measure_new],,DESC,Dense)

Finally, create a table object, select the [ID], measure [measure_new] as value levels. Add the measure [RANK] as Visual level Filters, let the RANK value is less than or equals to 2.

[tulsi]

So the measures that I have created are;

Measure1 = CALCULATE(SUM('all'[Qty]),ALLEXCEPT('all','all'[Task Name]))

Measure 2 = RANKX(ALLSELECTED('all'),[Measure1],,DESC,Dense) 

 and I created a table just like you mentioned with the filters i'm getting something like this:

But I need to get top 2 tasks for that particular month, here it's just showing overall top 2 tasks 

[themistoklis]

@tulsi

I think the measure:

Measure1 = CALCULATE(SUM('all'[Qty]),ALLEXCEPT('all','all'[Task Name]))

needs to be different.

Sum('all[Qty]) needs to be count of task names and 'all'[Task Name] needs to be 'all'[Months]

and the table needs to have the months as dimension and the measure1 and add RANKX on Visual Filters

[tulsi]

For some reason i'm not getting the desired result. I tried using your formula but i'm getting something like this:

Measure1 = CALCULATE(COUNT('all'[Task Name]),ALLEXCEPT('all','all'[month]))

Measure 2 = RANKX(ALLSELECTED('all'),[Measure1],,DESC,Dense)

Do you have any other solution?

[themistoklis]

@tulsi

I dont really understand the logic of your table.

You count task names and then you add them as a measure??!

I dont think it makes sense.

Best to send a sample of how you want the final table to look like

[tulsi]

I have done it on excel. 

This is how it looks

[tulsi]

[v-cherch-msft · Accepted solution]

Hi @tulsi

You may try to add a rank measure as below. Then drag it to visual level filter and set the measure is less than or equal to 2. If it is not your case, please share some data sample.

Rank =
RANKX ( ALL ( Table[task name] ), CALCULATE ( SUM ( Table[Qty] ) ) )

Regards,

Cherie

[tulsi]

It worked! Thank you!!!

[themistoklis]

@tulsi

Can you share the workspace with us or a sample of it?

[tulsi]

So I need a bar chart with top 2 task names ( by qty) for respective months.

It should look something like this just top 2 task names should be displayed.