Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Oros
Post Partisan
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
v-rongtiep-msft
Community Support
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])

vpollymsft_0-1668750990429.png

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.

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. 

 

Oros_0-1669135762057.png

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

Oros_1-1669136773642.png

 

 

 

 

v-rongtiep-msft
Community Support
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 (#)])))

vpollymsft_0-1667800802513.png

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.

Hi @v-rongtiep-msft ,

 

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

 

Here is the context:

 

Oros_0-1667851347893.png

 

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

Oros_1-1667851408112.png

 

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

 

Oros_2-1667852014169.png

 

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

vpollymsft_0-1667874994519.pngvpollymsft_1-1667875005059.pngvpollymsft_2-1667875016156.png

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.

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.

 

Oros_0-1667878330529.png

 

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

 

Oros_1-1667878557677.png

Oros_2-1667878709911.png

 

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

vpollymsft_0-1667886969850.pngvpollymsft_1-1667886988427.png

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.

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)

 

Oros_0-1667914481856.png

 

v-rongtiep-msft
Community Support
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])

 

vpollymsft_0-1667540408743.png

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.

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.

 

Oros_0-1667567816823.png

 

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.

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.

 

Oros_0-1667787203789.png

 

keshavagrawal27
Resolver I
Resolver I

Hello @Oros ,

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

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.