Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everybody,
I have this formula for this table (TABLE1) and it's work :
SUM1 = ADDCOLUMNS( ADDCOLUMNS( ADDCOLUMNS( SUMMARIZE('TABLE1';[WEEK];"Col 1";SUMX(FILTER('TABLE1';[WEEK]=EARLIER('TABLE1'[WEEK])&&('TABLE1'[NAME]="A"||'TABLE1'[NAME]="B"));[VAL])); "Col 2";SUMX(FILTER('TABLE1';[WEEK]=EARLIER('TABLE1'[WEEK])&&('TABLE1'[NAME]="C"||'TABLE1'[NAME]="D"));[VAL])); "Col 3";[Col 1]+[Col 2]/2); "Col 4";[Col 1]-[Col 3])
TABLE1 and SUM1
but I have an other table (TABLE2), with the sames values (TYPE = good) and other values (TYPE = bad)
I don't where modify my formula to have only (TYPE = good) ????
TABLE2
Thanks a lot for your help,
Joc
Solved! Go to Solution.
Hi @joc,
Just add the Type = good to your filter statement:
SUM2 = ADDCOLUMNS ( ADDCOLUMNS ( ADDCOLUMNS ( SUMMARIZE ( 'TABLE2', [WEEK], "Col 1", SUMX ( FILTER ( 'TABLE2', [WEEK] = EARLIER ( 'TABLE2'[WEEK] ) && ( 'TABLE2'[NAME] = "A" || 'TABLE2'[NAME] = "B" ) && Table2[TYPE] = "good" ), [VAL] ) ), "Col 2", SUMX ( FILTER ( 'TABLE2', [WEEK] = EARLIER ( 'TABLE2'[WEEK] ) && ( 'TABLE2'[NAME] = "C" || 'Table2'[NAME] = "D" ) && Table2[TYPE] = "good" ), [VAL] ) ), "Col 3", [Col 1] + [Col 2] / 2 ), "Col 4", [Col 1] - [Col 3] )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@joc,
You could also refer to the following DAX.
Table = ADDCOLUMNS ( GROUPBY ( TABLE2, TABLE2[WEEK], "Col 1", SUMX ( CURRENTGROUP (), IF ( TABLE2[TYPE] = "good" && TABLE2[NAME] IN { "A", "B" }, TABLE2[VAL] ) ), "Col 2", SUMX ( CURRENTGROUP (), IF ( TABLE2[TYPE] = "good" && TABLE2[NAME] IN { "C", "D" }, TABLE2[VAL] ) ) ), "Col 3", [Col 1] + [Col 2] / 2, "Col 4", - [Col 2] / 2 )
Thanks a lot for your responses ! 🙂
@joc,
You could also refer to the following DAX.
Table = ADDCOLUMNS ( GROUPBY ( TABLE2, TABLE2[WEEK], "Col 1", SUMX ( CURRENTGROUP (), IF ( TABLE2[TYPE] = "good" && TABLE2[NAME] IN { "A", "B" }, TABLE2[VAL] ) ), "Col 2", SUMX ( CURRENTGROUP (), IF ( TABLE2[TYPE] = "good" && TABLE2[NAME] IN { "C", "D" }, TABLE2[VAL] ) ) ), "Col 3", [Col 1] + [Col 2] / 2, "Col 4", - [Col 2] / 2 )
Hi @joc,
Just add the Type = good to your filter statement:
SUM2 = ADDCOLUMNS ( ADDCOLUMNS ( ADDCOLUMNS ( SUMMARIZE ( 'TABLE2', [WEEK], "Col 1", SUMX ( FILTER ( 'TABLE2', [WEEK] = EARLIER ( 'TABLE2'[WEEK] ) && ( 'TABLE2'[NAME] = "A" || 'TABLE2'[NAME] = "B" ) && Table2[TYPE] = "good" ), [VAL] ) ), "Col 2", SUMX ( FILTER ( 'TABLE2', [WEEK] = EARLIER ( 'TABLE2'[WEEK] ) && ( 'TABLE2'[NAME] = "C" || 'Table2'[NAME] = "D" ) && Table2[TYPE] = "good" ), [VAL] ) ), "Col 3", [Col 1] + [Col 2] / 2 ), "Col 4", [Col 1] - [Col 3] )
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |