RANKX with SUMMARIZECOLUMNS and Filter

[scaballerom]

Hi all,

Here is a sample of my dataset, where:

- Job: one job can have multiple tasks, and be executed more than once in a day (job timestamp repeated).

- Task: multiple tasks happen within a job.

- Job TimeStamp: date and time the job started. One job can have multiple executions within a day.

- Job Date: date the job started.

- Job Status: can be either G or C.

- Unique Key: concatenation between Job and Job TimeStamp.

Here is a table I constructed using the following measures:

G = CALCULATE(DISTINCTCOUNTNOBLANK(Table1[Unique Key]),Table1[Job Status]="G")

Distinct Count Unique Key = DISTINCTCOUNTNOBLANK(Table1[Unique Key])

%G = IF([G]>0,[G]/[Distinct Count Unique Key],0)

Average = CALCULATE([Distinct Count Unique Key],

REMOVEFILTERS(Table1[Job]))/CALCULATE(DISTINCTCOUNTNOBLANK(Table1[Job]),REMOVEFILTERS(Table1[Job]))

What I want to do a ranking of those jobs that are above average and below average.

The jobs that are above average (Distinct Count Unique Key > Average), I want to create a table that contains the following:

On the other hand, for those below average (Distinct Count Unique Key < Average):

This ranking should be filtered with a slicer by Job Date.

Ranking = 

VAR SummaryTable =
FILTER(
SUMMARIZECOLUMNS(Table1[Job],Table1[Job Date],
"job_executions",DISTINCTCOUNTNOBLANK(Table1[Unique Key]),
"job_failed_aborted",CALCULATE(DISTINCTCOUNTNOBLANK(Table1[Unique Key]),Table1[Job Status]="G"),
"pcte_job_failed_aborted",CALCULATE(DISTINCTCOUNTNOBLANK(Table1[Unique Key]),Table1[Job Status]="G")/DISTINCTCOUNTNOBLANK(Table1[Unique Key])),
[job_failed_aborted]>0)

RETURN
RANKX(ALL(SummaryTable[job_name]),[pcte_job_failed_aborted],,DESC)

But it is not working.

Could you please help me out?

Thanks a lot!

Best,

S

[scaballerom]

Hi @amitchandak ,

Thank you so much for your response; I tried to do as you mentioned, but got the following error:

Ranking =

VAR SummaryTable =
FILTER(
SUMMARIZE(Table1,Table1[Job],Table1[Job Date],
"job_executions",DISTINCTCOUNTNOBLANK(Table1[Unique Key]),
"job_failed_aborted",CALCULATE(DISTINCTCOUNTNOBLANK(Table1[Unique Key]),Table1[Job Status]="G"),
"pcte_job_failed_aborted",CALCULATE(DISTINCTCOUNTNOBLANK(Table1[Unique Key]),Table1[Job Status]="G")/DISTINCTCOUNTNOBLANK(Table1[Unique Key])),
[job_failed_aborted]>0)

RETURN
RANKX(SummaryTable,[pcte_job_failed_aborted],,DESC)

Error message: the value for 'pcte_job_failed_aborted' cannot be determined. Either the column doesn't exist, or there is no current row for this column.

[amitchandak]

@scaballerom , Create two measure like this and try

pcte_job_failed_aborted =Divide(CALCULATE(DISTINCTCOUNTNOBLANK(Table1[Unique Key]),Table1[Job Status]="G"),DISTINCTCOUNTNOBLANK(Table1[Unique Key]) )

Rank =

Rankx(
FILTER(
SUMMARIZE(allselected(Table1),Table1[Job],Table1[Job Date],
"job_executions",DISTINCTCOUNTNOBLANK(Table1[Unique Key]),
"job_failed_aborted",CALCULATE(DISTINCTCOUNTNOBLANK(Table1[Unique Key]),Table1[Job Status]="G")),
[job_failed_aborted]>0) , [pcte_job_failed_aborted] )

[scaballerom]

Hi @Amit,

That seems to work great (see image below).

However, how would you incorporate the condition about the comparison on the average?

When I incorporate more sample data (see image below):

And use condition: 

Is Above Avg. = IF([Distinct Count Unique Key]>Table1[Average],1,0) to sort whether the distinct values of jobs are above or below average, the ranking seems to not work:

Could you please help with this issue? If needed, I can provide both the Excel sample data amb PBI sample report.

Thanks so much.

Best,

S

[amitchandak]

@scaballerom , Use summarize and when you give a table name use all or allselected

example 

summarize(allselected(Table1), <Other columns>)