Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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êsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |