Please: filter in measure not working for categories not related to filter

[LilianDupont]

Hi everyone!

I would need some help on a DAX formula.

I have a table with prices of products: mine and other companies'.

In front of each product, I would like to display the product of my company so that I can compare it other product.

Yet, using the filter, it only displays the price in front of my product...

Could anyone give me a hint? 😕

Thanks a lot!!Example

[tamerj1]

Hi @LilianDupont 
Following to you last description of the problem, please find attached sample file with the proposed solution. Once you review the solution you will realize why did I ask all those questions.

An extra country filter table shall be created and connected with the original country column via inactive relationship as shown in the screenshot. The following screenshots are self explanatory

Sales Amount by Country = 
CALCULATE ( 
    [Sales Amount], 
    USERELATIONSHIP ( Countries[CountryRegion], Customer[CountryRegion] ), 
    REMOVEFILTERS ( Customer[CountryRegion] ) 
)

Sales Selected Countries = 
IF ( 
    [Sales Amount by Country] <> BLANK ( ),
    CALCULATE ( Sales[Sales Amount], ALLSELECTED ( Customer[CountryRegion] ) )
)

[LilianDupont]

Hi @tamerj1 !!

Thanks a looot for your answer and example!

I tried it and it worked when i tried to have the price by brand, but when I want it by Manufacturer it gives me the same mistake :s

I tried to send you back your PBI but did not find how to do so. Hence I send you the screenshot.

Please any questions/doubts don t hesitate to reach me!

Thanks again for your help!
Lilian

[tamerj1]

Hi @LilianDupont 
You can upload the file and share the link via dropbox, onedrive or anything similar. I think it is better if we connect in order to clearly inderstand your requirement.

[tamerj1]

@LilianDupont 

It is now clear. Thank you for explanation. 
It is clear to me why your getting blank but before jumping into solutions, please confirm the table name and column names of column that contains "My product", "other1" amd "other2" and the column that contains the countries. If not the same table please advise about the relationships. 

[LilianDupont]

I have 2 tables:

Product table, which is called D_Product with 2 columns among others: "ProductName" and "CompanyName"

Sales tables, which is called T_Market with columns: Country, ProductName, Units, Values

I create a formula Price = divide(values,units)

I guess I want to have 2 more:
1. Calculating prices only for the products of my company
2. Calculating the difference in % between the price of my products vs other products

[tamerj1]

@LilianDupont 
I think I did not explain my question properly.

In your matrix visual you are placing two columns in the the row of the matrix. One of the them is clearly the T_Market[Country] is that correct? What is the other column that classifies the products into "MyProducts, "Other 1" and "Other 2"?

[tamerj1]

Hi @LilianDupont 
If "Price" is a measure then you can write it
Price = SUMX ( Fact_Sales, DIVIDE ( Fact_Sales[Value], Fact_Sales[Units] ) )

[LilianDupont]

Hi @tamerj1 ,

I tried this but it does not work for my example I think 😕

Would you have another idea based on the additionnal detail I provided?

If you need some more don't hesitate!

Thanks a lot!!

Lilian

[tamerj1]

Hi @LilianDupont 

please provide sample row data

[LilianDupont]

Hi @tamerj1 !

Thanks for answering.

Let me share you an easy example so that it s easier for me to explain.

In my example, I basically try to find a measure that would allow me to display sales for Australia on all rows, even in front of countries which are not Australia.

I m currently using the "filter" parameter because I thought it would have worked like "AllSelected" but it does not...

Does the example help to understand the situation? 🙂 

[LilianDupont]

Hi @tamerj1 ,

Thanks a lot for your answer.

I ve been unprecise with my question, my apologies.

I would like to create a measure enabling to show the difference in % between the price of my product and the price of other products.

Yet, my formula (Vs.MyProduct) just shows the % in front of my product and of course displays 100%.

In front of other products, the filter D_Product="MyCompany" does not work.

Do you have an idea how I could handle it?

I attached a new example that I wish is better explained.

Thanks a lot,

Lilian