¿How i can make calculation including filters?

[JUSEGAPO]

Hi everyone,

Im new on it platform, i was working on Qlik platforms but in this moment we need to use Power BI. 

I need to calculate percetage of execution, so i have it information:

Numero ticket	Estado	Proyecto	Region
Ticket1	Waiting	Project1	Region1
Ticket2	Waiting	Project1	Region1
Ticket3	Waiting	Project1	Region1
Ticket4	Closed	Project1	Region1
Ticket5	Waiting	Project1	Region1
Ticket6	Waiting	Project1	Region1
Ticket7	Waiting	Project1	Region1
Ticket8	Closed	Project1	Region2
Ticket9	Waiting	Project1	Region2
Ticket10	Waiting	Project1	Region2
Ticket11	Waiting	Project1	Region2
Ticket12	Closed	Project1	Region2
Ticket13	Closed	Project1	Region2
Ticket14	Closed	Project1	Region3
Ticket15	Closed	Project1	Region3
Ticket16	Closed	Project2	Region3
Ticket17	Closed	Project3	Region3

I need make a formula to obtain the next values:

Formule 1: Total Ticket closed/total tickets (Project 1 and region 1)

Formule 2: Total ticket closed/total tickets (project 1 and region 2)

Formule 3: total ticket closed/total tickets (project 1 and region 3)

Expected values:

Formule 1: 1/7  14%

Formule 2: 3/6  50%

Formule 3: 2/2  100%

Thanks in advance...!!! 

 

[GilbertQ · Accepted solution]

Hi @JUSEGAPO

You can do it with the following measures below. 

NOTE: My table is named 'Table1'

Tickets = COUNTROWS('Table1')

Tickets Closed = CALCULATE([Tickets],'Table1'[Estado] = "Closed")

Tickets Closed % = DIVIDE([Tickets Closed],[Tickets])

I then put in a slicer for Proyecto and I got the following result when slicing on Project1

[JUSEGAPO]

Hi @GilbertQ,

Thanks a lot!!! It works.

Can you explain me how i can make more than 1 filter in the same formula? For example, make filter in "Estado" with "Closed" and "Finish"?

Thanks in advance.

[GilbertQ]

Sure not a problem at all.

To have multiple conditions you can modify it as shown below from the Original Measure

Tickets Closed = CALCULATE([Tickets],'Table1'[Estado] = "Closed" && [Tickets],'Table1'[Estado] = "Finish")

The && represents and, so it is saying where tickets are "Closed and Finish"

If you want to use OR then use the || which will then say tickets are "Closed or Finish"