cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Calculated Formula to count

Hello everybody, 

Today i have the following report on excel:opr january.PNG

On my Power BI already created different Tables: one with a calendar table, other with the 5 Countries, other with the 8 Areas. And the last one called MovementsReport with all the historical info of the movements of the comany since 2015 (all relationships are already created).

 

In my MovementsReport Table I have:

a column called Status, with values "OK", "NO OK", null.

a column called Area with the name of each area were the movement has been done.

a column called Country with the respective Country were the movement has been done.

a column with the date in which the movement has been donde.

 

I want it to be more dynamic than the actual Excel Report, so I'm using slicers for country, area, year and month. Instead of creating one table visualization i want to use cards as visualization, one for each value, as following... so I will be capable of giving the % a conditional formatting. So I should create two "tables" one to show the monthly info of movements using slicers. And other that should be static (no interactions) showing the accumulated for the current year.

 

power bi opr.PNG

 

 

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    

    

For the accumulated table, I would like them to be a calculate formula, filtering the area, the status, and the date.

For Example Count OK, if Area SUPPLY, for Argentina, Month JANUARY 2017 + FEB 2017 + MARCH 2017 +...

 

Thanks & regards!

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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!

 

View solution in original post

7 REPLIES 7
Highlighted
Microsoft
Microsoft

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.Smiley Happy

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

Highlighted

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.

 

count & sum.PNG

 

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!

 

 

Highlighted

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?

 

  

columns OPR.PNG

 

Regards!

 

Highlighted

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

Highlighted

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!

 

 

Highlighted

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

data count pegando en area vieja.PNG

 

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?

 

tabla pegando errado.PNG

 

regards!

 

 

Highlighted

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!

 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors