Forum Discussion

chrisgehm's avatar
chrisgehm
Icon for Helper III rankHelper III
8 years ago
Solved

Accumulated count if

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

 

MonthYearCond1Cond2Cond3
120170OpenA
120170OpenA
220170CloseA
220171OpenA
320171CloseB
320170OpenB

 

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!

Need Help
  • Vvelarde's avatar
    Vvelarde
    8 years ago

    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

     

     

4 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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's avatar
      chrisgehm
      Icon for Helper III rankHelper III
      8 years ago

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


      Thanks!!!


      Kind regards

  • BILASolution's avatar
    BILASolution
    Icon for Solution Specialist rankSolution Specialist
    8 years ago

    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's avatar
      chrisgehm
      Icon for Helper III rankHelper III
      8 years ago

      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