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!

     

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

  • franorio's avatar
    franorio
    Helper III
    9 years ago

    Hi v-ljerr-msft thanks for your reply!!!

    For the first formula, Mov Adherence, when I cross it with the results of the report done in excel, they doesn´t match. 

    First part before the "+"

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

    works, it gives me the exact same result before doing the addition of the other column on excel. 

     

     

    But using the complete formula isn't working, for example for January Area People should have 2 movements + 1 of the other column, it's returning me 154. or sales should be 6 + 3 and it's returning 23.

     

    Thanks!

     

     

  • franorio's avatar
    franorio
    Helper III
    9 years ago

    Hi  v-ljerr-msft

    I almost have it, 

    My DimArea Table (that I'm using as rows on a matrix) has a relationship with Área Anterior column. So when I use part of your formula, I'm getting it OK about the movements reflecting on each area.

     

    Problem is when I want to add (by companies policy) the lateral movements also to the new areas. When using this formula to count te Sumar CF column (next step a measure that add together both measures, and would be done)

     

    Count Sumar CF= CALCULATE(
    COUNTROWS('MovementsReport') ,
    FILTER(
    'MovementsReport',
    NOT ISBLANK('MovementsReport'[Sumar CF a])
    )
    )

     

    also getting ok the result of the count, but it's reflecting on the previous area because of the relationship, instead of returning on the new area.

     

    For january for example should have those 3 movements of Sumar CF column added as: Sales 2 and People 1

     

    But because of the Relationship of my DimArea table it's returning 2 for marketing previous Area, and 1 to Sales, as you can see on the image below. How can I amend it to count those values Sales with 2 and People with 1 as I need it?

     

     

    regards!

     

     

  • franorio's avatar
    franorio
    Helper III
    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!