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

Question

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.&nbsp;TABLE ACODEPRODCOUNTRYBALANCEBAND COLINDEXBBCTLENG807ML (P1)1BBCSSENG162ML (P1)2BBCTLUNK56ML (P1)3BBCSSUNK13ML (P1)4BBCSEENG1ML (P1)5BBCFLENG109FL (P1)6CRI2FLENG578&nbsp;7BBCPTENG836PTML (P1)8CRI2PTENG107&nbsp;9CRI1PTENG157&nbsp;10&nbsp;TABLE BDOMPTCOLSUB_AMOUNTINDEXBBCMSL01MPAMSL02MPAICRP103MPAICRP2-5734EUEFL-PRE 201205ENGFL06&nbsp;TABLE CDISTINCT_CODEINDEXML (P1)1FL (P1)2PTML (P1)3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Results TableDISTINCT CODE FROM TABLE CRESULTS COLML (P1)1039FL (P1)109PTML (P1)836&nbsp;&nbsp;Thanks for any sort of help.&nbsp;

ryan_mayu · Answer

samioberoi&nbsp;
you can try this

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

&nbsp;
pls see the attachment below

ryan_mayu · Answer

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 · Answer

Hi ryan_mayu,&nbsp;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.&nbsp;&nbsp;Actual Amended Results TableDISTINCT CODE FROM TABLE CRESULTS COLML (P1)1039FL (P1)-464PTML (P1)836Hope i could make it proper and clear this time and sorry for not explaining it properly in the first post.Thanks.

ryan_mayu · Answer

samioberoi&nbsp;
&nbsp;
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?&nbsp;

samioberoi · Answer

Hi,This is the DAX i used for Band Col below:BAND_COL = if (Table A[PROD] in {"TL","SS", "SE"}&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp;&nbsp;Table A[CODE] = "BBC"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp;&nbsp;Table A[COUNTRY] in {"ENG", "UNK"},&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"ML (P1),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;if (Table A[PROD] = "FL"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp;&nbsp;Table A[CODE] = "BBC"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp;&nbsp;Table A[COUNTRY] = "ENG",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"FL (P1),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;if (Table A[PROD] = "PT"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp;&nbsp;Table A[CODE] = "BBC"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp;&nbsp;Table A[COUNTRY] = "ENG"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"PTML (P1)"&nbsp;&nbsp;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&nbsp;"ML (P1)",&nbsp;"PTML (P1)”.&nbsp;Measure =VAR LT =SWITCH(&nbsp; &nbsp; &nbsp; &nbsp; TRUE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECTEDVALUE(Table A[PROD] in {"TL","SS", "SE"}&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp;&nbsp;SELECTEDVALUE(Table A[CODE] = "BBC"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp;&nbsp;SELECTEDVALUE(Table A[COUNTRY] in {"ENG", "UNK"},&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"ML (P1)",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SELECTEDVALUE(Table A[PROD] = "FL"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp;&nbsp;SELECTEDVALUE(Table A[CODE] = "BBC"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp;&nbsp;SELECTEDVALUE(Table A[COUNTRY] = "ENG",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"FL (P1)”,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;SELECTEDVALUE(Table A[PROD] = "PT"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp;&nbsp;SELECTEDVALUE(Table A[CODE] = "BBC"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp;&nbsp;SELECTEDVALUE(Table A[COUNTRY] = "ENG"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"PTML (P1)”VAR X90 = CALCULATE(&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUM(TABLE A [BALANCE]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TREATAS(VALUES(TABLE C [DISTINCT COUNT], TABLE A [BUCKET COL])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )VAR TBVALUE = CALCULATE(&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LOOKUPVALUE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TABLE B [SUB_AMOUNT],&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TABLE B [DOM] = “MPA”&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TABLE B [PTCOL] = “ICRP2”&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ))RETURNIF(&nbsp; &nbsp; LT = "FL (P1)”,&nbsp;&nbsp; X90 + IF (ISBLANK (TBVALUE, 0, TBVALUE),LT&nbsp;&nbsp;Hope i could explain better and for any info please let me know.&nbsp;Appreciate a lot for your effort to help.&nbsp;

Forum Discussion

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

8 Replies

Related content

SUMMARIZE TABLE with specific conditions ?

Conditional column with specific values

Specific matrix

Derive an End date

Calculation Group + ISSELECTEDMEASURE and Derived Measures

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

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

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