Measure based on specific value

[Aoi]

Hi,

*I'm Japanese, so my English might be a bit strange.

I have two table:

 1. Sales data (fact)

 2. Reference price (dimention)

Table 1 

Table 2

This table provides reference unit prices by sales quantity to perform appropriate pricing.

These tables have relationship on "Product Name".

Then I want to create such a table below;

 - The aggregation of the data is summarized by Client Group.

 - Create a mesure that based summury of quantity and reference price corresponding applicable quantety.

I created a measure bleow but the table with this measure is not working correctly.

*This code is in Japanese, so I will provide a simplified version below.

Reference Price =
if([Quantity]<max('Table 2'[Applicable quantity 2]),
   max('Table 2'[Reference Price 1],

  if(and([Quantity]>=max('Table 2'[Applicable quantity 2]),
         [Quantity]<max('Table 2'[Applicable quantity 3])),
         max('Table 2'[Reference Price 2])),
          
         if(                   //'omittion

A table with this measure returns a nonsensical answer...
If I filter at Product Name = [aaa], this table is returning rows with product names that have no relation and both Client Group and Reference Price have values that are different from the actual ones, and Quantity is blank. Onlu the first row is correct.

Thanks a lot for your help..!

Aoi

[v-zhangti]

Hi, @Aoi 

You can try the following methods.

Measure = 
Var _quantity=SUM(Table1[Quantity])
Var _quantity_1=CALCULATE(MAX(Table2[Applicable quantity 1]),FILTER(Table2,[Product Name]=SELECTEDVALUE(Table1[Product Name])))
Var _Price_1=CALCULATE(MAX(Table2[Reference Price 1]),FILTER(Table2,[Product Name]=SELECTEDVALUE(Table1[Product Name])))
Var _Price_2=CALCULATE(MAX(Table2[Reference Price 2]),FILTER(Table2,[Product Name]=SELECTEDVALUE(Table1[Product Name])))
Return
IF(_quantity<_quantity_1,_Price_1,IF(_quantity>_quantity_1,_Price_2))

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Aoi]

Thank you for your reply!

I tried this measure, but  an error below occured...

it exceeded the resources available to the visual 

 
It maybe becouse of my bad data modeling.

So I will review that.

Thanks a lot for your help...!