topic Help on Measure Selected in DAX Commands and Tips

Help on Measure Selected

orghn — Thu, 17 Feb 2022 14:32:21 GMT

Hi Everyone!

I need to select only 1 lab and N products for many Labs. I need to find the price per month of my product (my_price) and compare to the total_prod_price of each prod to find the difference (%) between my product and the others.

Sample file: https://www.dropbox.com/s/jh9ctxatt079dvq/Sample%28Updated2%29%20%281%29.pbix?dl=0

Expected output:

Product A, my _price=1600, total_prod_price = 1100, dif = 1100/1600, month = Oct

Product B, my _price=1600, total_prod_price = 1600, dif = 1600/1600, month = Oct

Product C, my _price=1600, total_prod_price = 2100, dif = 2100/1600, month = Oct

Thanks!

Re: Help on Measure Selected

Anonymous — Mon, 21 Feb 2022 03:39:29 GMT

Hi orghn ,

Based on my understanding,

Select Lab B, the related Product is B,E
Select Product Name A,B,C
Since the same Product based on 1 and 2 is B, so my_price=Product B=1600

If so,please firstly delete the relationship between Lab Table and Product Table:

Then create the measure:

my price measure = var _allProds=SUMMARIZE(FILTER(ALL('Product'),[LabName] =MAX('Lab'[Lab Name])),[ProductName]) return CALCULATE(SUM('Price'[Price]),FILTER(ALLSELECTED('Price'),[Product] in _allProds))

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

Re: Help on Measure Selected

orghn — Mon, 21 Feb 2022 13:27:56 GMT

Hi Anonymous ,

Thanks for your help but I cant delete that relationship. And I also need to select N months

Re: Help on Measure Selected

Anonymous — Tue, 22 Feb 2022 01:17:37 GMT

Hi orghn ,

1.but I cant delete that relationship. You could create a new Lab table for the slicer:

New Table = VALUES(Lab[Lab Name])

Measure = var _allProds=SUMMARIZE(FILTER(ALL('Product'),[LabName] =MAX('New Table'[Lab Name])),[ProductName]) return CALCULATE(SUM('Price'[Price]),FILTER(ALLSELECTED('Price'),[Product] in _allProds))

diff = [Measure]/[Total_Prod_Price]

2. And I also need to select N months So what's your expected when select multiple months?

On my side:

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