Forum Discussion

MV13's avatar
MV13
Frequent Visitor
8 years ago
Solved

TOP N filter across previous months based on current Month

Hi All   I am trying to create a report where the user uses a date slicer (Eg 01/01/2014 - 04/08/2015) to filter the report, I have a number of categories that I would like to display within a stac...
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi OwenAuger 

Thanks a lot for the solution. It helped me. I was struggling to resolve this problem for some days. But now I'm having a second problem. I am using this example to filter the top 10 also  based on the amount... but I want to show the % variation month by month, and when I change this part:

 

// Return Value Sum filtered to those Categories

RETURN CALCULATE ( [Value Sum], KEEPFILTERS ( TopCategories ) )

to this:

// Return Value Sum filtered to those Categories

RETURN CALCULATE ( [% variation month by month], KEEPFILTERS ( TopCategories ) )

 

the measure lose it self and go back to show all the categories.

Here is my [% variation month by month] calculation:

 

VAR vValueLastMonth =
CALCULATE(
    [Value Sum]
    ,PARALLELPERIOD( DIMDATA[Data], -1, MONTH )
)

VAR vVariation =
DIVIDE(
    [Value Sum]
    ,vValueLastMonth
)

RETURN
vVariation - 1
 
 
 
What am I doing wrong?
Thanks in advance.
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi OwenAuger,

I have a third problem.

I'm trying to use those measures inside a Field Parameter, that will switch between TopN based on [Value Sum] from current month (like your explanation) and [Value Sum] of all data, so I can choose with a field parameter, which one I prefer to see on the chart.

I did some changes and it is like this:

 

// Get Top 10 Categories in that month
VAR TopCategories =
    CALCULATETABLE (
        TOPN ( 10, ALL ( Data[Category] ), ALL ( 'Calendar Date'[Date] ) ) )
    )

 

But it is filtering in a weird way, because there is not all categories, but there are more than 10. I think it is choosing the Top 10 from each month, and not from all data.