count filterted value of a colum e.g. like in Excel by using countifs formula

[Anonymous]

Dear experts,

I have table where two columns are matter of interest and try to count filtered value based by columns data.

Time	Country	Population
1/19	CAN	Increased
2/19	CAN	No Change
3/19	CAN	Increased
1/19	US	Increased

I'm interested to know e.g. count of Population Increased in a specific country.

Example: Home many times Popoluation has increased in CAN?  Answer is 2

In Excel this can be calulated by using "countifs" function 

= countifs($Population:$Population;"Increased";$Country:$Country;"CAN")

Can similar calculation done in PowerBI?

[AlB · Accepted solution]

Hi @Anonymous 

You can create a measure and place it in a card visual:

Measure =
CALCULATE (
    COUNT ( Table1[Population] ),
    Table1[Population] = "Increased",
    Table1[Country] = "CAN"
)

You could also create a simpler and more versatile measure:

Measure 2 = COUNT ( Table1[Population] )

and then use a slicer in Table1[Population] and another one on Table1[Country] to select  "Increased" and "CAN" (or any other combination you need)

[AlB · Accepted solution]

Hi @Anonymous 

You can create a measure and place it in a card visual:

Measure =
CALCULATE (
    COUNT ( Table1[Population] ),
    Table1[Population] = "Increased",
    Table1[Country] = "CAN"
)

You could also create a simpler and more versatile measure:

Measure 2 = COUNT ( Table1[Population] )

and then use a slicer in Table1[Population] and another one on Table1[Country] to select  "Increased" and "CAN" (or any other combination you need)

[Anonymous]

Great, thanks this will do the trick.

Now I need to figure out how to value those better and present at the same table, where increased count get's value 100 and no change value 50

Country	Population	Count	Value to look for	Note
CAN	Increased	2	200	"Increased" Count multiplied * 100
CAN	No change	1	50	"No change"Count multiplied * 50
US	Increased	1	100	"Increased" Count multiplied * 100

Not sure if that can done at one column.

[AlB · Accepted solution]

Try this:

1. Place Table1[Country] and Table1[Population] in a table visual.  Make sure both are displayed with the 'Don't summarize' option.

2. Place the measure we created before, [Measure 2], in the table visual. Note we already have Country and Population in the filter context in each row.

3. Create this measure (the 'value to look for') and place it in the table visual:

Measure 3 =
VAR _Factor =
    SWITCH (
        SELECTEDVALUE ( Table1[Population] ),
        "Increased", 100,
        "Decreased", 50
    )
RETURN
    _Factor * [Measure 2]

[Anonymous]

Hi @AlB 

Works like a charm.

You're true super user.

Thanks