cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
scaballerom
Helper I
Helper I

RANKX with SUMMARIZECOLUMNS and Filter

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.

scaballerom_0-1658150650347.png

 

Here is a table I constructed using the following measures:

scaballerom_2-1658150882340.png

GCALCULATE(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:
scaballerom_3-1658151183941.png

 

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

scaballerom_4-1658151205097.png

 

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
4 REPLIES 4
scaballerom
Helper I
Helper I

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.

@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] )

Hi @Amit,

 

That seems to work great (see image below).

scaballerom_0-1658160287741.png

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

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

scaballerom_4-1658161807304.png

 

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:
scaballerom_5-1658161844330.png


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
Super User
Super User

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

 

example 

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.