CODEWITHSUNDEEP
powerbi
user14104064
user14104064

Reputation:

Power BI counting double values

I'm trying to create a measure in Power BI that will count doubles as a single value and then later add them all up to see how many doubles we have. Here is an example:

enter image description here

Each customer whose name shows up more than once should be counted as 1 Bonus question, how can I make a measure which will count customers whose name only shows up once (example name - Sarah).

Thanks in advance!

Upvotes: 0

Views: 1417

Answers (2)

mkRabbani
mkRabbani

Reputation: 16908

You can create this below measure to check the customer is there for once or multiple time in the list. This measure will return 1 if the customer is there for once and 0 if the customer exists for multiple time.

is_unique = 

VAR current_customer = MIN(your_table_name[customer])

VAR customer_count = 
CALCULATE(
    COUNT(your_table_name[customer]),
    FILTER(
        ALL(your_table_name),
        your_table_name[customer] = current_customer 
    )
)

RETURN IF(customer_count = 1, 1, 0)

This will return 1 for customer- Sarah and David. For all other customer, it will return 0. Now, if you add the above measure to a Card and apply SUM on the measure, it will return value 2 which is basically your customer count with single existence.

Upvotes: 0

Aldert
Aldert

Reputation: 4313

If you want to count the customers distinct you can use:

CountCustomers = DISTINCTCOUNT([Customer])

if you want to count the doubles, you can use:

Doubles = COUNTROWS(FILTER(SUMMARIZE(CusTable, CusTable[Customer], "countC", COUNTROWS(CusTable)), [countCol] > 1))

First I summarize it to a table with the name of the customer and how often it is appearing in the table Next I filter this table by all rows bigger than 1 Last I count the rows

Upvotes: 1

Related Questions