cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Partisan

## Dividign 2 measures always gives 100%

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])

14 REPLIES 14
Community Support

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.

Post Partisan

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):

Community Support

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.

Post Partisan

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:

Community Support

Hi @Oros ,

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.

Post Partisan

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).

Community Support

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.

Post Partisan

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)

Community Support

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.

Post Partisan

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.

Community Support

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).

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.

Post Partisan

Sorry for the confusion.

Here is the data with only 2 tables

And here is the simple illustration.  Thanks again.

Resolver I

Hello @Oros ,

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

Post Partisan

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.