Determine Bottom 3 based on Max Month and Showing history of the selected Bottom 3

[asa70]

Hi all,

I hope you are all well.

I am currently working on a survey analyis report and have been tasked to show the lowest 3 ranking questions on a particular month (or MAX Month) when the survey was conducted. I have looked at previous forums based on TOPN and RANKX just to see if I am doing something incorrect.

Unfortunately, I can't share the raw data because of the high sensitivity. However, I have created some data for this question. This is basically the outcome I'm looking for: 

As you can see, the bottom 3, based on Oct 2024 was Q3, Q5 and Q7:
- I need to be able to see the entire history based on those 3 Questions as shown in the figure labeled as "History"

- The same would apply if the survey looks at May 2024 as the Max Month, I would then look at the bottom 3 of that selected value month.

I have used both RANKX and TOPN measures with different results:

1. Measure = RANKX(FILTER(ALLSELECTED(Table),Table[Month]=MAX(Table[Month]),SUM(Table[Value]),,ASC)

2. Measure =
VAR _bottom3 = TOPN(3,FILTER(ALLSELECTED(Table),Table[Month]=MAX(Table[Month]),SUM(Table[Value]),ASC)
RETURN
CALCULATE(SUM(Table[Value]),KEEPFILTERS(_bottom3)

What I have seen is that:
1. Need to identify the the bottom 3 ranking questions based on MAX selected month

2. Create a virtual list and place those 3 questions inside the list

3. Calculate the history of those 3 questions if they appear in the timeline (match the questions in the selected table visual with the questions in the virtual list).

Any help or ideas would be highly appreciated and welcome.

Kind Regards,

[asa70]

@saud968 Thank you so much, I have used your solution. I did change it at the end, as I did not use the flag. Instead, I added a temp table:

VAR _temptable = CALCULATETABLE(VALUES(Table[Question]),_bottom3)
RETURN
CALCULATE(SUM(Table[Value]),FILTER(Table, Table[Month] = MAX(Table[Month]) && Table[Question] IN _temptable))

[saud968]

I am glad it helped, please do accept my solution. And I love that you changed things to help yourself further. 

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[Kedar_Pande]

Measure:

Bottom3Questions = 
VAR MaxMonth = MAX(Table[Month])
VAR Bottom3 =
TOPN(
3,
FILTER(
ALL(Table),
Table[Month] = MaxMonth
),
Table[Value],
ASC
)
RETURN
CONCATENATEX(Bottom3, Table[Question], ", ")

Measure:

HistoryOfBottom3 = 
VAR SelectedMonth = MAX(Table[Month])
VAR Bottom3QuestionsList =
TOPN(
3,
FILTER(
ALL(Table),
Table[Month] = SelectedMonth
),
Table[Value],
ASC
)
RETURN
CALCULATE(
SUM(Table[Value]),
FILTER(
Table,
Table[Question] IN VALUES(Bottom3QuestionsList[Question])
)
)

Create a Table Visual that:
Displays the original questions.
Shows the calculated measure of Bottom3Questions to see which are included.
Showcases the HistoryOfBottom3 measure for the complete historical data.

If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[saud968 · Accepted solution]

To Identify the Bottom 3 Questions for the Max Month: Create a measure to rank the questions based on their values for the maximum month:
RankMeasure =
RANKX(
FILTER(
ALLSELECTED('Table'),
'Table'[Month] = MAX('Table'[Month])
),
SUM('Table'[Value]),
,
ASC
)

Create a Measure to Identify the Bottom 3 Questions: Use the TOPN function to get the bottom 3 questions:
Bottom3Questions =
VAR MaxMonth = MAX('Table'[Month])
VAR Bottom3 =
TOPN(
3,
FILTER(
ALLSELECTED('Table'),
'Table'[Month] = MaxMonth
),
SUM('Table'[Value]),
ASC
)
RETURN
CALCULATETABLE(
VALUES('Table'[Question]),
Bottom3
)

Create a Measure to Flag the Bottom 3 Questions: Use this measure to flag the bottom 3 questions across all months:
IsBottom3 =
VAR Bottom3List = [Bottom3Questions]
RETURN
IF(
'Table'[Question] IN Bottom3List,
1,
0
)

Visualize the History of the Bottom 3 Questions: Create a visual (e.g., a line chart) to show the history of the bottom 3 questions. Use the IsBottom3 measure to filter the data:
Add Question and Month to the axis.
Add Value to the values.
Apply a visual-level filter to only include rows where IsBottom3 is 1.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!