Sorting table with multiple columns

[Anonymous]

Hi All,

I need to create a Line and Stacked chart for which multiple columns will be derived using the following conditions:

Table name: Survey

Steps/Logic:

calculate the no. of surveys
Group by Support Group
Get the total count of rows
Sort the values by total responded surveys
Select only Top 50% = hence total rows/2
Take the 50% rows 
Sort it by highest % negative response 
Now display top 5 values in the chart 

Example data

Group	# of survey	Negative response %
A	148	0.68%
B	67	1.49%
E	19	5.26%
H	38	2.63%
C	37	2.70%
G	705	0.99%
D	17	5.88%
F	32	3.13%
H	5	20.00%
L	6	16.67%
K	11	9.09%
N	24	8.33%
I	20	10.00%
J	109	4.59%
M	130	1.54%

[parry2k]

@Anonymous will this "% negative response" will be aggregated for final output?

[Anonymous]

Hello @parry2k 

Thank you for the reply. No, it won't be aggregated. Aggregation is by Group only.

[parry2k]

@Anonymous  is this expected output?

[Anonymous]

Exactly!!! Am I missing something? I tried multiple ways but I am in one step behind the solution. Can you please explain?

[parry2k · Accepted solution]

@Anonymous solution attached, look at table1 in attached, it has few columns and measures. you can play with it as per your need, didn't had much time to work on it thou but I guess you will get idea from it.

[Anonymous]

Hi @parry2k 

The below doesn't work and the Y/N column is empty for my case:

#_TOP = 
var x = TOPN([TOTAL_ROWS], VALUES('Table'[A), [B], DESC) 
RETURN
 IF('Table'[A] IN x, "Y")

Table is the table name, A, B are the columns. B is the column need to be sorted. Total_Rows is the rows to be taken in Top N.

Once I get the results, I need to rank the result set by Column C and get only Top 5 Rank. Can you please help on this issue? Thanks!

[parry2k]

@Anonymousdoes the solution attached previosuly is not working?

[Anonymous]

Hi @parry2k 

Thank you for the reply. I am getting ciurcular dependency 😞 

Below are the 2 screenshots for your reference. 

The table is created using a DAX expression which is as below:

Table = 
VAR current_month= [#_LATEST_MONTH]

RETURN
SUMMARIZE(CUSTSURVEY, CUSTSURVEY[ASSIGNEE_SUPPORT_GROUP],

"#_of_Survey", 
CALCULATE(COUNT(CUSTSURVEY[ENTRY_ID]), FILTER(CUSTSURVEY, [#_GET_MONTH_AND_YEAR] = current_month && [#_IS_SURVEY_RESPONDED] = "Yes")), 

"% Negative Reviews", 
 (CALCULATE (
        COUNT ('CUSTSURVEY'[ENTRY_ID]),
        FILTER (
            'CUSTSURVEY',
            (
                'CUSTSURVEY'[#_GET_MONTH_AND_YEAR] = current_month
                    && (('CUSTSURVEY'[OVERALL_EXP] = 0)||('CUSTSURVEY'[OVERALL_EXP] = 1))
                    && NOT ISBLANK ('CUSTSURVEY'[OVERALL_EXP]) 
            )
        )
    )
        / CALCULATE (
            COUNT ( 'CUSTSURVEY'[ENTRY_ID] ),
            FILTER (
                'CUSTSURVEY',
                ( 'CUSTSURVEY'[#_GET_MONTH_AND_YEAR] = current_month
                    && ( 'CUSTSURVEY'[#_IS_SURVEY_RESPONDED] = "Yes" ) )
            )
        )
    )
)

Can you tell me what am missing? Thanks!

[parry2k]

@Anonymous when you get chance, can you PM me?