topic Find value in another table in DAX Commands and Tips

Find value in another table

Dennis_Pedersen — Wed, 19 Jun 2024 09:49:38 GMT

I have a measure which have calculated the total discount on a customer(cust_disc).

Given this discount I would like to find from the table below(discounttable) what the turnover should have been at least with this discount. For example with a discount of 48,1% the turnover should be at least 300.000

How would you write that measure to find the value 300.000?

Re: Find value in another table

bhanu_gautam — Wed, 19 Jun 2024 10:50:07 GMT

Dennis_Pedersen , Try using below DAX

Minimum Turnover =
VAR Discount = [cust_disc] // Replace [cust_disc] with your actual measure for calculating the total discount on a customer

RETURN
CALCULATE(
SUM(discounttable[Turnover]),
discounttable[Discount] >= Discount,
FILTER(
ALL(discounttable),
discounttable[Discount] >= Discount
)
)

Re: Find value in another table

Dennis_Pedersen — Mon, 24 Jun 2024 13:18:09 GMT

Thanks a lot. That helped me in the right direction.

However I had to ammend it a little, with making a discount from and discount to, to make it work correct.

VAR Discount = [Total discount_ %]

RETURN
CALCULATE(
[Amount from],
Discount >=DIM_Segmentering[Discount from],
FILTER(
ALL(DIM_Segmentering),
Discount<DIM_Segmentering[Discount to]
)
)