COUNT based on a measure

[Anonymous]

Hello Guys, I'm brand new to Power BI and DAX and need some Help to execute the followin.

I have the following table

Client	Target of Calls	Number of Calls Made	Above Target
Sales Person 1	18	19	1
Client A	10	9	No
Client B	8	10	Yes
Sales Person 2	13	13	1
Client C	5	6	Yes
Client D	7	7	No

The [Target of Calls] is a calculated Measure that I create this way: Target of Calls = COUNT(PlannedCalls[CALL_ID])
The [Number of Calls Made] is also a calculated Measure calculated by the formula: Number of Calls Made = COUNT(Calls[Call_ID])

I need to create a new measure to identify how many Clients were Called ABOVE target .... The last column of above table. I was able to create a column to identify if it's above Target or Not .... but now I'm not able to aggregate it on the SalesPerson level .... any idea how do it ?

Thanks in advance

[parry2k · Accepted solution]

@Anonymous not sure what does that means but I guess it answered your original question, no?

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[v-xulin-mstf]

Hi @Anonymous

Try measure as:

Count Above = 
IF(
    HASONEVALUE('Table'[Client]),
    IF(MAX('Table'[Number of Calls Made])>MAX('Table'[Target of Calls]),"Yse","No"),
    IF(
        SUMX(FILTER(ALL('Table'),'Table'[Sales Person]=MAX('Table'[Sales Person])),'Table'[Number of Calls Made])>SUMX(FILTER(ALL('Table'),'Table'[Sales Person]=MAX('Table'[Sales Person])),'Table'[Target of Calls]),
        "Yes",
        "No"
    )
)

Here is the output:

The demo is attached, please try it.

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[Anonymous]

Thanks mate, but Almost there but at Sales Person line we should display the "count" of Yes , is it possible ?

Also, have in mind that there were filters apllied to Clients field

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

Measure1 = countrows(filter(values(Customers[Customer name]),[Number of calls]>[Target of calls]))

Drag Measure 1 to a card visual.

[Anonymous]

Guys,

based on your solutions, I inserted the "IF( NOT ISBLANK" and it did the trick

teste1 = COUNTROWS((FILTER(VALUES(Customers[Customer name]);IF(NOT ISBLANK([Target of calls]);[Number of calls]=[[Target of calls]]))))

[Anonymous]

Hi Ashish..... 

Well, I think I have missed something. ...... your formula did the same as the @parry2k did ...

The thing is that there is a filter .... for Client A and Client B for instance ... As per the test I did , its summing the clients despite the filter, só te total on Sales Person is "wrong"

I'm sorry I cannot share database print as it's my company confidential

[Ashish_Mathur]

Hi,

I cannot help you without seeing your file.

[parry2k]

@Anonymous I'm still confused, you need to show the result using my measure, paste sample data, show the result what is working and what is not working? I'm shooting here in the dark with very little information 

[parry2k · Accepted solution]

@Anonymous not sure what does that means but I guess it answered your original question, no?

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

@parry2k part of It, I can make the YES or NO but the part in red below its summing all the register in client table, despite the filterw I'm using in the view

Client	Target of Calls	Number of Calls Made	Above Target
Sales Person 1	18	19	1
Client A	10	9	No
Client B	8	10	Yes
Sales Person 2	13	13	1
Client C	5	6	Yes
Client D	7	7	No

[parry2k]

@Anonymous try this measure

Count Above = 
SUMX ( VALUES ( Table[Client Column] ), IF ( [Number of Calls] > [Target of Calls], 1, 0 ) )

and use it in the visual.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

@parry2k  almos there .... the only thing is that the total row for the salesrep is summing the whole table ... 

maybe I need to use some other parameter to achieve it .

and thanks a lot for the help already