Specific value to be added from a value derived from a conditional value

[samioberoi]

Hi,

I need some help please. I have got three tables as below. In Table A, I created a calculated column called as BAND COL below and the relationship between the tables is as created below between Table A and Table B on INDEX column and between Table A and Table C it is inactive relationship created on BAND COL and DOM. I want to create a measure, which should calculate with the logic as if it is BBC from code column and if it is TL, SS and SE from Prod column and if it is ENG and UNK from Country column then it should just normally add up those values from BALANCE column, but if it is BBC from code column and it is PT from PROD column and it is ENG from Country column then it should take that aggregated value and add it with the other particular value from Table B where it is MPA from DOM column and it is ICRP2 from PTCOL . The reason I created the Dimension table Table C is so that it will filter the results similarly from all other tables linked to it.

TABLE A

CODE	PROD	COUNTRY	BALANCE	BAND COL	INDEX
BBC	TL	ENG	807	ML (P1)	1
BBC	SS	ENG	162	ML (P1)	2
BBC	TL	UNK	56	ML (P1)	3
BBC	SS	UNK	13	ML (P1)	4
BBC	SE	ENG	1	ML (P1)	5
BBC	FL	ENG	109	FL (P1)	6
CRI2	FL	ENG	578		7
BBC	PT	ENG	836	PTML (P1)	8
CRI2	PT	ENG	107		9
CRI1	PT	ENG	157		10

TABLE B

DOM	PTCOL	SUB_AMOUNT	INDEX
BBC	MSL	0	1
MPA	MSL	0	2
MPA	ICRP1	0	3
MPA	ICRP2	-573	4
EUE	FL-PRE 2012	0	5
ENG	FL	0	6

TABLE C

DISTINCT_CODE	INDEX
ML (P1)	1
FL (P1)	2
PTML (P1)	3

Results Table

DISTINCT CODE FROM TABLE C	RESULTS COL
ML (P1)	1039
FL (P1)	109
PTML (P1)	836

Thanks for any sort of help.

[v-xinc-msft]

Hi @samioberoi ,

Did the replies above offered help you solve the problem, if it helps, you can consider to accept it as a solution so that more user can refer to, or if you have other problems, you can offer some information so that can provide more suggestion for you.

Best regards,

Lucy Chen

[samioberoi]

Hi ryan_mayu,

Thanks for your reply and apologies for the mistake from my side in the Results Table. It should be like if it is specifically FL (P1) then it should take 109 from Table A and add/subtract that with -573 from Table B and should give me its value equal to something like 109 + (-573) = -464 and filtering should me done in the visual by the DISTINCT_CODE column from Table C. Rest of the codes like ML (P1) and PTML (P1) should be calculated normally. 

Actual Amended Results Table

DISTINCT CODE FROM TABLE C	RESULTS COL
ML (P1)	1039
FL (P1)	-464
PTML (P1)	836

Hope i could make it proper and clear this time and sorry for not explaining it properly in the first post.
Thanks.

[ryan_mayu]

@samioberoi 

since I can't find and relationship between FL(P1) row in Table A and the -573 in the table B, how did you get the band col? could you pls share the DAX? 

[samioberoi]

Hi,
This is the DAX i used for Band Col below:

BAND_COL = if (Table A[PROD] in {"TL","SS", "SE"}
                      && Table A[CODE] = "BBC"
                      && Table A[COUNTRY] in {"ENG", "UNK"},
                       "ML (P1),

                      if (Table A[PROD] = "FL"
                      && Table A[CODE] = "BBC"
                      && Table A[COUNTRY] = "ENG",
                       "FL (P1),

                      if (Table A[PROD] = "PT"
                      && Table A[CODE] = "BBC"
                      && Table A[COUNTRY] = "ENG"
                       "PTML (P1)"

And this measure below is what i tried to create for the required calculation i am trying to get the right results for. It seems to be giving the right value for FL (P1) where the sum needs to be done from a figure from Table A with a value from Table B, but i can't get the other values as normal for "ML (P1)", "PTML (P1)”.

Measure =

VAR LT =

SWITCH(

        TRUE(

                      SELECTEDVALUE(Table A[PROD] in {"TL","SS", "SE"}
                      && SELECTEDVALUE(Table A[CODE] = "BBC"
                      && SELECTEDVALUE(Table A[COUNTRY] in {"ENG", "UNK"},
                       "ML (P1)",

                     SELECTEDVALUE(Table A[PROD] = "FL"
                      && SELECTEDVALUE(Table A[CODE] = "BBC"
                      && SELECTEDVALUE(Table A[COUNTRY] = "ENG",
                       "FL (P1)”,

                      SELECTEDVALUE(Table A[PROD] = "PT"
                      && SELECTEDVALUE(Table A[CODE] = "BBC"
                      && SELECTEDVALUE(Table A[COUNTRY] = "ENG"
                       "PTML (P1)”

VAR X90 = CALCULATE(
                     SUM(TABLE A [BALANCE]),

                    TREATAS(VALUES(TABLE C [DISTINCT COUNT], TABLE A [BUCKET COL])
                    )

VAR TBVALUE = CALCULATE(

           LOOKUPVALUE(

                              TABLE B [SUB_AMOUNT],

                               TABLE B [DOM] = “MPA”

                              TABLE B [PTCOL] = “ICRP2”

                             ))
RETURN

IF(

    LT = "FL (P1)”,

   X90 + IF (ISBLANK (TBVALUE, 0, TBVALUE),

LT

Hope i could explain better and for any info please let me know. 

Appreciate a lot for your effort to help. 

[ryan_mayu]

still can't find any relationship between TABLE A and B.could you pls explain why FLP1 will minus -573? I can't find any related columns in both tables.

[samioberoi]

Hi ryan_mayu,

I created index column in both Table A and Table B to be able to make one to one relationship happen between the two. Beween the Table A and Table C (which i created to be able to dynamically filter based on DISTINCT_CODE column between the other tables as well) there is an inactive relationship for DISTINCT_CODE column from Table C and BAND_COL from Table A.  Something like as i am trying to show in the image below. I may be doing something wrong. 
It can be FLP1 will plus -573 (From Table B). The last DAX above called Measure is calculating FLP1 + (-573) properly, the only thing it is not doing is that it doesn't give me the aggregated values calculated for "ML (P1)", "PTML (P1)”.

Me being not so knowledgeable about PBI i definitely am doing something wrong somewhere.

Much appreciated!

[ryan_mayu]

@samioberoi 

the index are not matching for FLP1 and -573. One is index 4 and the other is 6. Still don't know the logic of calculation. If FLP1 will always add the subtotal in table B, you can try 

=if (selectedvalue(DISTINCT_CODE) ="FL(P1)", XXXX) 

[ryan_mayu]

@samioberoi 

you can try this

Measure = SUMX(FILTER('Table A','Table A'[BAND COL]=MAX('Table C'[DISTINCT_CODE])),'Table A'[BALANCE])

pls see the attachment below