Use an ID column to filter two columns from two tables to only show the data with matching IDs

[natabird3]

Dear all,

I am trying to create a single measure (if possible) that will show only values that are matching. I have two measures to get Sales A and Sales B (some filters are applied on both to get the exact number for a specific brand and country). Using those two if i place in a table i get the following output:

ID		Sales A		Sales B		B/A
238438645131807		131807
238437950184106		184106
238446771109200		109200
238433154547907		547907
238436988427907		427907
238436988227807		227807
238434171911006		911006
238462637979202		979202
238444318061303		911006		61303		6.73%
238462166649802		649802
238432494142206		142206
238441701090402		90402
238429192316407		316407
238446562392500		392500
238432947363206		363206
238446045118900		118900
238432077172207		172207
238451403600800		600800		392500		65.33%
238434300836403		836403
238436228352702		352702
238445936068100		68100
238462166399102		399102
238446798659203		659203
238432947429606		429606
238434697186400		186400
238438489797505		797505
238446959668500		668500
238433179660503		660503
238429739945506		945506
238468288179902		179902
238438650756901		668500
238441113550405		756901		659203		87.09%

The problem is that one of the columns doesnt have values for every ID and i am only interested in the columns that do have values. If i place a filter to not show blank of the visual i resolve the problem, however is there a way to do it with a measure? Basically, telling the calcualtion to only match the once that have data in both columns and ignore the blank. As if i leave it like this and calculate the last column i will get a very low % value as the data is assuming 0 for the blanks, while if i filter to not show blank is what the value i am trying to get with this measure. Hope this explains what i am trying to do.

Thanks for the help in advance. 

[natabird3 · Accepted solution]

So i was able to get finally the desired result:
CALCULATE(divide([Sales B],[Sales A],BLANK()),FILTER(ALLSELECTED('Hierarchy'[ID]),[Sales B]<>BLANK()))

[amitchandak]

@natabird3 , try like

new sales A= if(isblank([Sales B]), blank(),[Sales A])
Or
new sales A= sumx(Values(Table[ID]),if(isblank([Sales B]), blank(),[Sales A]))

[natabird3]

with this measure i am telling if a value is blank in column B i assume the value is A. However, i want to ignore this row completely and only use when we have values in both A and B if that makes sense.

[natabird3]

Basically, I am trying to get the data to get filter down to this level:

ID		Sales A		Sales B		B/A
238444318061303		911006		61303		6.73%
238451403600800		600800		392500		65.33%
238441113550405		756901		659203		87.09%

So that if i calcualte the last measure B/A i am getting only the IDs that have values in both A and B columns.

[Fowmy]

@natabird3 

Your B/A Measure can be modified to show as below. I did not quite understand the exact requirement though.

New B/A = 

IF( ISBLANK(Sales A) || ISBLANK(Sales A), BLANK(), B/A )

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[natabird3]

With the proposed measure i am still getting the same result. What i am trying to do is ignore the blank entries and only calcuate the once that are included in both columns A and B.

[amitchandak]

@natabird3 , try like

new sales A= if(isblank([Sales B]) || isblank([Sales A]), blank(),[Sales A])
Or
new sales A= sumx(Values(Table[ID]),if(isblank([Sales B]) || isblank([Sales A]) , blank(),[Sales A]))

----

new sales A= if(isblank([Sales B]), blank(),[Sales A])
Or
new sales A= sumx(Values(Table[ID]),if(isblank([Sales B]), blank(),[Sales A]))