topic Re: Need help to calculate a measure based on another measure in DAX Commands and Tips

Need help to calculate a measure based on another measure

Touaibia — Fri, 10 Dec 2021 07:33:53 GMT

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

Re: Need help to calculate a measure based on another measure

jppv20 — Fri, 10 Dec 2021 08:06:24 GMT

Hi Touaibia ,

Can you share some example data and expected output?

Re: Need help to calculate a measure based on another measure

Touaibia — Fri, 10 Dec 2021 08:08:36 GMT

How i can share a data example please ?

Re: Need help to calculate a measure based on another measure

jppv20 — Fri, 10 Dec 2021 08:17:26 GMT

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

Re: Need help to calculate a measure based on another measure

Touaibia — Fri, 10 Dec 2021 08:43:01 GMT

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

Re: Need help to calculate a measure based on another measure

jppv20 — Fri, 10 Dec 2021 10:17:12 GMT

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.

Re: Need help to calculate a measure based on another measure

Touaibia — Fri, 10 Dec 2021 11:11:58 GMT

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.

Re: Need help to calculate a measure based on another measure

jppv20 — Fri, 10 Dec 2021 11:22:23 GMT

So what should be the output with this example data?

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

Re: Need help to calculate a measure based on another measure

Touaibia — Fri, 10 Dec 2021 11:40:31 GMT

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

Re: Need help to calculate a measure based on another measure

Anonymous — Tue, 14 Dec 2021 06:21:52 GMT

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