Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply

Find value in another table

Hi 

 

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?

 

Dennis_Pedersen_0-1718790270098.png

 

1 ACCEPTED SOLUTION
bhanu_gautam
Super User
Super User

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






View solution in original post

2 REPLIES 2
bhanu_gautam
Super User
Super User

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






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

 

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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