Where not equal to in Table chart

[psk180590]

Hello All,

I have a table with data as below

Product

AA

BB

CF

HG

Market

HG

IJ

KL

How do i select products where Product is NOT EQUAL to Market. My final result should be

Product

AA

BB

CF

HG

HG

IJ

KL

[sunny_talwar]

May be this?

=Sum({<Product = {"=Len(Trim(Product)) > 0"}>}[Sales 1])/Sum({<Product = {"=Len(Trim(Product)) = 0"}>}[Sales 1])

[psk180590]

stalwar1

In your Application for the Table if we also consider Product as a dimension then every thing goes away.

[psk180590]

What i would want is to have Market, Area and Product as dimensions and Sum of Sales , Sales of Units as my Formulas.

We are so near to this, if we can get the Product as a dimension we are done. But, i'm not sure how such a small issue is troubling me from so long

[sunny_talwar]

May be this?

=If(Len(Trim(Product)) > 0, Sum({<Product = {"=Len(Trim(Product)) > 0"}>}[Sales 1])/Sum(TOTAL <Market>{<Product = {"=Len(Trim(Product)) = 0"}>}[Sales 1]))

[psk180590]

Thanks much Sunny. This almost solves my problem.

One last thing can you tell me how to get rid of the blank value as seen at top in your image

[sunny_talwar]

Disable total

[psk180590]

stalwar1‌

When i try to include all the other dimensions in this table as i mentioned above this doesn't work.

Can you please help me with this.

Area      Market      Product      Date    Sales(1+2+3)  Units(1+2+3)

Area   Market   Product   and Date should be my dimensions.  The rest two should be my calculated formulas.

[sunny_talwar]

Can you provide the output you are hoping to see with your provided sample?

[psk180590]

Sorry for not being clear, as i stated above:

Dimensions:

Area - All the numbers listed under column Area

Market  -  All the values listed under column Market

Product - Only the values which are not blank/null

Year- All the years of dates listed under column date

Measures:

Sales Product - Total of sales where Product is not null

Sales Market- Total of sales where Product is null

% - (Sales Product)/(Sales Market)

Please, let me know if it is still not clear.

Thanks Much!!

[sunny_talwar]

When I say output, I am looking for the numbers you would want to see and now the definition. The problem is that I can give you expression which gives out some value, but it won't match what you want. When you give me numerical values, I can look for ways to get those numbers.

HTH

Best,

Sunny