Need help to calculate a measure based on another measure

[Touaibia]

Hello everyone,

I have this data model

Client table with: IdCustomer / SalesRep / Area / CustomerProfil

ProductTable : IdProduct / ProductName / 24StrategicProduct 

SalesTable: IdCustomer /IdProduct  / Sales

I want to analyze the performance of 24 strategic products that my company want to push up.

The column 24StrategicProduct contains “true” or “false” statement, which allows me to isolate my 24 strategic products

1 - I want to calculate for each customer the count of strategic products he had purchased.

    I did this: NB24 = calculate ( distinctcount(SalesTable [IdProduct]), ProductTable[24StrategicProduct]="true")

And it Works, when I set up a table with all my customers, I do have the count of my strategic products each client has purchased.

2- Now I want to set up a table with my CustomerProfil (which contains only 3 values A B C ) and add a measure that returns the number of customers who have purchased at least one time all my 24 strategic products.

I tried this :

CustomersNb24 = calculate ( distinctcount(SalesTable [IdCustomer]),FILTER(SalesTable, NB24=24))

And it doesn't work

I also tried to use an intermediate formula

Achv_24 = if (NB24=24,1,0)

Then

Calculate ( distinctcount(SalesTable [IdCustomer]),FILTER(achv_24 = 1))

Still doesn't work

I'm stuck on this formula since a week 😞

Tnx 

[Anonymous]

Hi @Touaibia,

Did the reference measure expression include some specific filters or calculate based on particular row contexts?

If that is the case, I think you need to manually add the filter effect on these formulas to reproduce that calculation result or they will get the wrong result if you use them to calculate with not related contexts.

In addition, you can also take a look at the following blogs about row/filter context and reference multiple measure expressions:

Optimizing DAX expressions involving multiple measures - SQLBI

Row Context and Filter Context in DAX - SQLBI

Regards,

Xiaoxin Sheng

[jppv20]

@Touaibia you can create a table here and put in some example data, doesn't have to be the actual numbers

[Touaibia]

sales_table : 

IdCustomer	IdProduct	Sales
1	100	50£
22	120	40£
9	200	10£
4	300	20£
7	200	21£

...etc

ProductTable : 

IdProduct	ProductName	24StrategicProduct
100	Dummy1	True
120	Dummy2	True
200	Dummy3	True
300	Dummy4	False

...etc

Client table :

IdCustomer	SalesRep	Area	CustomerProfil
1	John	Paris	A
2	John	Paris	A
3	John	Paris	B
4	Marc	Lyon	C
5	Elodie	Nice	A

Our reps have to make sure that our customers purchase at least one time all of our 24 strategic product 

And I want to set up a table with : 

CustomerProfil	number of customers who ordered all our 24 strategic product
A	120
B	50
C	60
Total	230

Soo I want to evaluate whether or not my customers had purchased all of my 24 strategic products if yes count all this clients and tell, well I have 230 who had purchased my 24 strategic product.

Tnx

[jppv20]

@Touaibia ,

Can you explain how you get this result?

A - 120

B - 50
C - 60

I don't think I completely understand what you are trying to achieve here.

[jppv20]

Hi @Touaibia ,

Can you share some example data and expected output?

[Touaibia]

I want to calculate the number of customers who had purchased all my 24 staretgic product.

and A-120 B-50 C-60 is just a simulation of the output i'm expecting. 

[jppv20]

So what should be the output with this example data?

A - 1, B - 0, C - 0?

[Touaibia]

I want to calculate the number of customers who ordered all my  24 strategic products

[Touaibia]

How i can share a data example please ?