Dividign 2 measures always gives 100%

[Oros]

Hello.

I have 2 measures:

Salespersons number of assigned customers = (example, 50)

Number of customers who bought selected products = (example, 10)

How do you get the result to be = 20%?

Ratio = 20% (20% of assigned customers bought)

This is the measure that I applied.  It always gives 100% even if only selected products are filtered.

RESULT MEASURE =

% CUSTOMERS =

DIVIDE([CUSTOMERS WHO BOUGHT], [ASSIGNED CUSTOMERS])

[v-rongtiep-msft]

Hi @Oros ,

Please refer to my pbix file to see if it helps you.

Create measures.

NUMBERS OF CUSTOMERS WHO BOUGHT = CALCULATE(COUNT('customer bought'[customers who bought]),ALLSELECTED('customer bought'[sold product]))

measure = 
var _table=CALCULATETABLE(VALUES('customer bought'[customers who bought]),ALLSELECTED('customer bought'[sold product]),ALLSELECTED('Table'[SALESperson]))
var _salesperson=IF(MAX('Table'[customer]) in _table,1,0)
var _sal=CALCULATETABLE(VALUES('Table'[SALESperson]),FILTER(ALL('Table'),'Table'[customer] in _table),ALLSELECTED('Table'[SALESperson]))
var _4=IF(MAX('Table'[SALESperson]) in _sal,1,0)
return 
_4

number of customers assigned to salesperson = SUMX(('Table'),[measure])

Measure 4 = var _sales= CALCULATETABLE(VALUES('Table'[customer]),ALLSELECTED('Table'[SALESperson]))
var _sold=CALCULATETABLE(VALUES('customer bought'[customers who bought]),FILTER(ALL('customer bought'),'customer bought'[customers who bought] in _sales),ALLSELECTED('customer bought'[sold product]))
return 
IF(MAX('customer bought'[customers who bought]) in _sold,1,0)

total of number customer qho bought = SUMX('customer bought',[Measure 4])

TOTAL CUSTOMERS = COUNTAX(ALL('Table'),'Table'[customer])

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Oros]

Hi @v-rongtiep-msft ,

Thank you for your reply.  I put the solution in a grid.  The only remainng problem is 1 column (assigned to salesperson).  For example, Bob has 2 assigned customers but shows zero in the grid...the same thing with Frank. 

The correct table must display the ASSIGNED to salesperson column as (green numbers):

[v-rongtiep-msft]

Hi @Oros ,

Please refer to my pbix file to see if it helps you.

Create measures.

assigned to salesperson = CALCULATE(DISTINCTCOUNT('sales table'[CUSTOMER #]),FILTER(ALL('sales table'),'sales table'[ASSIGNED SALESPERSON (#)]=SELECTEDVALUE('sales table'[ASSIGNED SALESPERSON (#)])))

customers who bought = var _customer=SELECTEDVALUE('sales table'[PRODUCT])
var _1=CALCULATE(MAX('sales table'[CUSTOMER #]),FILTER(ALL('sales table'),'sales table'[PRODUCT]=_customer&&'sales table'[ASSIGNED SALESPERSON (#)]=SELECTEDVALUE('sales table'[ASSIGNED SALESPERSON (#)])))
return 
CALCULATE(COUNT('sales table'[PRODUCT]),FILTER(ALL('sales table'),'sales table'[CUSTOMER #]=_1))

total assigned customers = CALCULATE(DISTINCTCOUNT('sales table'[CUSTOMER #]),ALL('sales table'))

total customers who bought = CALCULATE(COUNT('sales table'[CUSTOMER #]),FILTER(ALL('sales table'),'sales table'[ASSIGNED SALESPERSON (#)]=SELECTEDVALUE('sales table'[ASSIGNED SALESPERSON (#)])))

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Oros]

Hi @v-rongtiep-msft ,

I think that your solution is very close.  Thank you again for your help.

Here is the context:

If all products and all salespersons are selected, this will be the result:

If a product is selected, this should be the result:

[v-rongtiep-msft]

Hi @Oros ,

Please have a try.

Please check there is no relationship between the tables.

Create measures.

measure =
VAR _table =
    CALCULATETABLE (
        VALUES ( 'customer bought'[customers who bought] ),
        ALLSELECTED ( 'customer bought'[sold product] ),
        ALLSELECTED ( 'Table'[SALESperson] )
    )
VAR _salesperson =
    IF ( MAX ( 'Table'[customer] ) IN _table, 1, 0 )
VAR _sal =
    CALCULATETABLE (
        VALUES ( 'Table'[SALESperson] ),
        FILTER ( ALL ( 'Table' ), 'Table'[customer] IN _table ),
        ALLSELECTED ( 'Table'[SALESperson] )
    )
VAR _4 =
    IF ( MAX ( 'Table'[SALESperson] ) IN _sal, 1, 0 )
RETURN
    _4

Measure 2 = SUMX(('Table'),[measure])

NUMBERS OF CUSTOMERS WHO BOUGHT =
CALCULATE (
    COUNT ( 'customer bought'[customers who bought] ),
    ALLSELECTED ( 'customer bought'[sold product] )
)

TOTAL CUSTOMERS = COUNTAX(ALL('Table'),'Table'[customer])

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Oros]

Hi @v-rongtiep-msft ,

Thank you for your reply.  It looks like it's finally working, with a just a minor adjustment.

Based o nthe seleciton below for Apple and Larry:  Only one of his customers bought apple (measure ), the total customers assigned to Larry is 3 (Measure 2), the total number of customers who bought apple is 3 and the total customers overall remain at 10.  This result table is correct.

But if 2 products are selected (apple and banana), the column measure, Larry sold apple to 1 customer and banana to another customer.  The measure column should have a total of 2 instead of 1 (results table below).

[v-rongtiep-msft]

Hi @Oros ,

I  am sorry for the misunderstand. The measure is used to calculate to get the [measure2]. I have created another measure for the result.

Measure 4 = var _sales= CALCULATETABLE(VALUES('Table'[customer]),ALLSELECTED('Table'[SALESperson]))
var _sold=CALCULATETABLE(VALUES('customer bought'[customers who bought]),FILTER(ALL('customer bought'),'customer bought'[customers who bought] in _sales),ALLSELECTED('customer bought'[sold product]))
return 
IF(MAX('customer bought'[customers who bought]) in _sold,1,0)

Measure 5 = SUMX('customer bought',[Measure 4])

The measure4 is used to get the [measure5].

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Oros]

Hi @v-rongtiep-msft ,

Thank you again for your help. 

I think that there's only one missing - the total customers assigned to salesperson/s.  In the example below, 2 customers bought banana and they are Larry's customers.  So there should be a column that is equal to 3 (Larry's total assigned customers).

Let's say 1 customer of Bob also bought banana, then the numbers will be:

Total Customers who bought = 3

Salespersons' Total assigned customers = 5 (3 customers assigned to Larry and 2 customers assigned to  Bob)

[v-rongtiep-msft]

Hi @Oros ,

I have created a simple sample, please refer to it to see if it helps you.

Create measures.

Measure = VAR _SELE=SELECTEDVALUE('Table'[CUSTOMER])
var _re=IF(_SELE=BLANK(),BLANK(),1)
return _re

Number of customers = CALCULATE(COUNT('Table'[CUSTOMER]),FILTER(ALLSELECTED('Table'),[Measure]=1))

ASSIGNED CUSTOMERS = CALCULATE(COUNT('Table'[BOUGHT]),ALL('Table'))

result = DIVIDE([Number of customers],[ASSIGNED CUSTOMERS])

If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Oros]

HI @v-rongtiep-msft 

Thank you very much for your quick reply.  Your solution is almost perfect.  There is just probably one thing or context that is missing why it still does not work.

Here is the full context and current measures that I have that do not work:

DISTINCT CUSTOMERS (BOUGHT SELECTED ITEMS) = CALCULATE(DISTINCTCOUNT(SALES TABLE[CUSTOMERS]),FILTER('SALES TABLE',[Sales]>0))

TOTAL ASSIGNED CUSTOMERS (TO A SALESPERSON) = DISTINCTCOUNT(Customer_Card[No]) 

TOTAL ASSIGNED CUSTOMERS (EXCLUDING NO SALES)  = CALCULATE(DISTINCTCOUNT(Customer[No]),FILTER('SALES TABLE',[Sales]>0))

When an item is selected, the third column value should NOT change.

[v-rongtiep-msft]

Hi @Oros ,

I'm a little confused. I can't reproduce your mistake, can you provide pbix file without privacy information and desired output with more details? (Or some sample data and a screenshot with your desired output).

How to Get Your Question Answered Quickly 

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Oros]

Hi @v-rongtiep-msft,

Sorry for the confusion.

Here is the data with only 2 tables

https://docs.google.com/spreadsheets/d/1b-JeseNitWMYtcAf2F9sLLfxJ1aUI11T/edit?usp=sharing&ouid=10436...

And here is the simple illustration.  Thanks again.

[keshavagrawal27]

Hello @Oros ,

check it with a table visual with one column as your %customer measure and other column as salepersons

[Oros]

Hi @keshavagrawal27 ,

Thank you for your reply.  The visual table always gives me 100% for % customers.  Even if the Number of customers who bought changes based on the product selected. 

For example, without the selection made on products, 

NUMBER OF CUSTOMER WHO BOUGHT =40

ASSIGNED CUSTOMERS = 40

%CUSTOMER = 100%

But when I select a few products only,

NUMBER OF CUSTOMER WHO BOUGHT = 10

ASSIGNED CUSTOMERS = 40

the result is still 100%

%CUSTOMER = 100%

Thanks again.