Segregating information using criteria for a single month

[gadielsolis]

Hello community.

I'm attempting to categorize some groups based on quartiles. I have four quartiles (Top, Mid, Mid Bottom, Bottom), and the quartile is calculated using a measure like this: (Quartile= IF(Score>0.9,"Top",IF(Score>0.8,"Mid",IF(Score>0.7,"Mid Bottom",IF(Score<0.7,"Bottom")))).

I have a slicer that allows me to segregate the quartiles for a specific timeframe.

My issue is as follows: I need to show the performance of the group that ended in quartile one during the month of September (for example) in November and December. If I filter the three months, the visual will show only the advocates whose scores for the three months belong to the top quartile. How can I exhibit the performance for the following months for groups that belong to a quartile in a specific month?

Thank you in advance for the support.

[gadielsolis]

Hello, 

I've created an example to provide you more details.

The table on the left is the score for that month and the table on the right is the quaritile based on that score.

What I would like to do is have the capacity to filter the names that ended in a specific quartile for a specific month (Let's say Tops for September) and show the performance for that specific group in the following months.

Sharing with you the file https://drive.google.com/file/d/15XQN4v0aYNeh8R98d-MMK0X_XnYfwIE1/view?usp=sharing.

Thank you in advance for the support.

[v-jialongy-msft · Accepted solution]

 Hi @gadielsolis 

First, I've added a calculated column to your table

Quartile1 = IF('Sheet1'[Score]>0.9,"Top",IF('Sheet1'[Score]>0.8,"Mid",IF('Sheet1'[Score]>0.7,"Mid Bottom",IF('Sheet1'[Score]<=0.7,"Bottom"))))

You can then create a new slicer and drag the month into the slicer

You can use the dax below to get the employee's score for the next month

Next Month Performance = 
VAR CurrentMonth = MONTH ( SELECTEDVALUE( 'Sheet1'[Month] ) )
VAR Next_Month = CurrentMonth + 1
VAR CurrentName = SELECTEDVALUE ( 'Sheet1'[Name] )
RETURN
CALCULATE (
    SUM ( 'Sheet1'[Score] ), 
    FILTER (
        ALL('Sheet1'),
        MONTH ( 'Sheet1'[Month] ) = Next_Month &&
        'Sheet1'[Name] = CurrentName
    )
)

You can use the dax below to get the employee's quartile for the next month

Next Month Quartile = 
VAR CurrentMonth = MONTH ( SELECTEDVALUE( 'Sheet1'[Month] ) )
VAR Next_Month = CurrentMonth + 1
VAR CurrentName = SELECTEDVALUE ( 'Sheet1'[Name] )
RETURN
CALCULATE (
    MAX ( 'Sheet1'[Quartile1] ), 
    FILTER (
        ALL('Sheet1'),
        MONTH ( 'Sheet1'[Month] ) = Next_Month &&
        'Sheet1'[Name] = CurrentName
    )
)

If you want a score for the last two or three months, you can use a similar approach

Here’s the result you want

^{Best Regards,}

^Jayleny

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[gadielsolis]

That works, thank you so much 🙂

[v-jialongy-msft]

Hi @gadielsolis 

In response to your question, I have created the following table

You can create the measure quartile by using the following formula

Measure = IF(MAX('Table'[Score])>0.9,"Top",IF(MAX('Table'[Score])>0.8,"Mid",IF(MAX('Table'[Score])>0.7,"Mid Bottom",IF(MAX('Table'[Score])<=0.7,"Bottom"))))

You can use the following formula to get the score for the next month

NextMonthScore = 
 var _date= EDATE(MAX('Table'[date]),1)
 RETURN CALCULATE(SUM('Table'[Score]),FILTER(ALL('Table'),'Table'[date]=_date))

If you want a score for the last two or three months, you can use a similar approach

Here’s the result you want

If I misunderstand what you mean, please provide some example input data and outputs

^{Best Regards,}

^Jayleny

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[Fowmy]

@gadielsolis 

Sharing a dummy Power BI file representing your scenario would be beneficial. You can save the Power BI file on Google Drive or any other cloud storage platform and provide the link here. Kindly ensure that permission is granted to open the file.

[andrewpirie]

Are you able to share more details of how the data and what the problem looks like, for example, a screenshot of a table with example data and a screenshot of this issue occurring on the report?

Also, I think Score is a measure, if so, are you able to provide the code for the measure? I'm worried it may be a measure that is returning a value you don't intend it to. You'd be able to test this by adding a maximum value to that IF condition - e.g.  IF([Score]>0.9 && [Score] <= 1 ,"Top" and seeing if you get a different result.