Accumulated count if

[chrisgehm]

Hi everyone!

I'm having some troubles while calculating some values.

I was doing it in excel with dynamic tables, but now I want to make a step forward and start using Power BI 🙂

I have this table

Month	Year	Cond1	Cond2	Cond3
1	2017	0	Open	A
1	2017	0	Open	A
2	2017	0	Close	A
2	2017	1	Open	A
3	2017	1	Close	B
3	2017	0	Open	B

What I need is the following:

I need show per Month & Year this division:

Count(Cond1 = 0 && Cond2 = Open && Cond3 = A) / Count (Cond1  = 0 && Cond2 = Open)

Is it possible?

Kind regards!

[Vvelarde · Accepted solution]

@chrisgehm

Hi, you can try to obtain your result with this:

Count to Cond1 is 0 and Cond2 Open and Cond3 is A =
COUNTROWS (
    FILTER (
        Table1,
        Table1[Cond1] = 0
            && Table1[Cond2] = "Open"
            && Table1[Cond3] = "A"
    )
)

Count to Cond1 is 0 and Cond2 Open =
COUNTROWS ( FILTER ( Table1, Table1[Cond1] = 0 && Table1[Cond2] = "Open" ) )

Result =
DIVIDE (
    [Count to Cond1 is 0 and Cond2 Open and Cond3 is A],
    [Count to Cond1 is 0 and Cond2 Open],
    BLANK ()
)

With 3 measure or All together in a single Measure

Measure =
VAR Count_to_Cond1_is_0_and_Cond2_Open_and_Cond3_is_A =
    COUNTROWS (
        FILTER (
            Table1,
            Table1[Cond1] = 0
                && Table1[Cond2] = "Open"
                && Table1[Cond3] = "A"
        )
    )
VAR Count_to_Cond1_is_0_and_Cond2_Open =
    COUNTROWS ( FILTER ( Table1, Table1[Cond1] = 0 && Table1[Cond2] = "Open" ) )
RETURN
    DIVIDE (
        Count_to_Cond1_is_0_and_Cond2_Open_and_Cond3_is_A,
        Count_to_Cond1_is_0_and_Cond2_Open,
        BLANK ()
    )

Regards

Victor

Lima - Peru

[chrisgehm]

Hi @Vvelarde It worked with the 3 mesasures separated. With the other the result it made no sense.

Thanks!!!

Kind regards

[BILASolution]

Hi @chrisgehm

Try this...

Meaures:

0, Open and A = IF(ISBLANK(CALCULATE(COUNTROWS(Table1);Table1[Cond1] = 0;Table1[Cond2] = "Open";Table1[Cond3] = "A"));0;CALCULATE(COUNTROWS(Table1);Table1[Cond1] = 0;Table1[Cond2] = "Open";Table1[Cond3] = "A"))

0 and Open = IF(ISBLANK(CALCULATE(COUNTROWS(Table1);Table1[Cond1] = 0;Table1[Cond2] = "Open"));0;CALCULATE(COUNTROWS(Table1);Table1[Cond1] = 0;Table1[Cond2] = "Open"))

Division = IF(ISBLANK(DIVIDE([0, Open and A];[0 and Open]));0;DIVIDE([0, Open and A];[0 and Open]))

Regards

BILASolution

[chrisgehm]

Hi @BILASolution!

Thanks for the answer.

It seems to be working, but at some piont I can notice an error:

This is my data set

So, with the first 2 measures, I've got 67 and 34

But when I make the division, it makes no sense, since the result is 79.83. It should be 0.50

Is there any mistake I'm making?

Kind regards