Forum Discussion

franorio's avatar
franorio
Helper III
9 years ago
Solved

Calculated Formula to count

Hello everybody,  Today i have the following report on excel: On my Power BI already created different Tables: one with a calendar table, other with the 5 Countries, other with the 8 Areas. And...
Need Help
  • franorio's avatar
    franorio
    9 years ago

    Hi v-ljerr-msft!

    Sorry to bother you again. I created a secondary relationship (dotted line) between my DimArea Table and my Area column of MovementsReport. I need to use the following formula

     

    COUNT of sumar cf = CALCULATE(
    COUNTROWS('MovementsReport') ,
    FILTER(
    'MovementsReport',
    NOT ISBLANK('MovementsReport'[Sumar CF a])
    )
    )

     

    and add a USERELATIONSHIP(DimAreas[Área],MovementsReport[Area])

     

    to add this value to Area Column and not Previous Area (Active relationship).

     

    I don't know why, when i create this measure i'm getting an error, too many arguments for filter. Could you please help me to get the right formula.

     

    Regards!

     

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi franorio,

My question is, which formula shoud I use for the measures?

What I need to show is:

a value Mov Adherence = count of OK Status

a value for Total Mov = count of OK/NO OK status


According to your descriptions above, you should be able to use the formulas below to create two measures, then show them on Card visuals or show them on Table/Matrix visuals with other columns.:smileyhappy:

Mov Adherence =
CALCULATE (
    COUNTROWS ( 'MovementsReport' ),
    FILTER ( 'MovementsReport', 'MovementsReport'[Status] = "OK" )
)

Total Mov =
CALCULATE (
    COUNTROWS ( 'MovementsReport' ),
    FILTER (
        'MovementsReport',
        'MovementsReport'[Status] = "OK"
            || 'MovementsReport'[Status] = "NO OK"
    )
)

 

Regards

  • franorio's avatar
    franorio
    Helper III
    9 years ago

    Hi v-ljerr-msft

    Thanks for your reply, just another question..

    Whats this report does is to measure the movements that are OK by policy inside the company, 

    Don't ask why, but because of our company policy, if the movement is inside the area it counts as 1, if the movement is from an Area as People to another Area for example Finance it should count as a movement adherence for each Area, so it count as 1 for each area. 

     

    What I did is to add new column, if Previous Area (Área Anterior) does not match New Area it returns me the column Sumar CF, that would be the Area where i should also add a movement.

     

     

    So for example, with the formula you gave me, i also would need to add the values of this Sumar CF column to each area.

    For this pic for example 

     

    Count if  OK by Área Anterior (status OK not shown on the image):

     

    Supply  13  +  1 (the one in Sumar CF column)

    People  5

    Sales  2

    Finance  2

    Marketing 1

    Solutions 2  +  1 (the one in Sumar CF column)

    L&CA  0  +  1 (the one in Sumar CF column)

     

    Is there a way to do this?

     

    Thanks & Regards!

     

     

    • franorio's avatar
      franorio
      Helper III
      9 years ago

      Just to clarify, 

       

      this is how my information looks:

       

      If Colun Área Anterior and column Area doesn't match = false returns Area on Column Sumar CF.

       

       

      so the count of "OK" Status para Smar CF should be added to the count of "OK" for Status

       

      Is there a way to add together two different calculate countrows?

       

        

       

      Regards!

       

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        Hi franorio,

         

        Could you try the formula below to see if it works in your scenario?

         

        Mov Adherence =
VAR selectedTable =
    CALCULATETABLE ( 'MovementsReport' )
RETURN
    CALCULATE (
        COUNTROWS ( 'MovementsReport' ),
        FILTER ( 'MovementsReport', 'MovementsReport'[Status] = "OK" )
    )
        + COUNTROWS (
            FILTER (
                ALL ( 'MovementsReport' ),
                CONTAINS (
                    selectedTable,
                    'MovementsReport'[Sumar CF], 'MovementsReport'[Área Anterior]
                )
            )
        )

        Total Mov =
VAR selectedTable =
    CALCULATETABLE ( 'MovementsReport' )
RETURN
    CALCULATE (
        COUNTROWS ( 'MovementsReport' ),
        FILTER (
            'MovementsReport',
            'MovementsReport'[Status] = "OK"
                || 'MovementsReport'[Status] = "NO OK"
        )
    )
        + COUNTROWS (
            FILTER (
                ALL ( 'MovementsReport' ),
                CONTAINS (
                    selectedTable,
                    'MovementsReport'[Sumar CF], 'MovementsReport'[Área Anterior]
                )
            )
        )

         

        Regards