Evaluation of data

[fahadfarooqi]

I have a sample data table to understand a problem.

PLAYER NAME	TEAM	YEAR JOINED	MONTH	YEAR	GROSS SCORE	NET SCORE	Category
ABC	XXX	2012	1	2016	444	1	649
ABC	XXX	2012	1	2016	444	1	658
ABC	XXX	2012	1	2016	444	1	668
ABC	XXX	2012	1	2016	444	1	671
ABC	XXX	2012	1	2016	444	1	1031
ABC	XXX	2012	1	2016	234	67	594
ABC	XXX	2012	1	2016	234	67	625
ABC	XXX	2012	1	2016	234	67	649
ABC	XXX	2012	1	2016	234	67	658
ABC	XXX	2012	1	2016	234	67	671
ABC	XXX	2012	1	2016	234	67	1031
ABC	XXX	2012	1	2016	234	67	1045
ABC	XXX	2012	1	2016	721	432	658
ABC	XXX	2012	1	2016	721	432	671
ABC	XXX	2012	1	2016	721	432	1031
ABC	XXX	2012	1	2016	972	654	625
ABC	XXX	2012	1	2016	972	654	658
ABC	XXX	2012	1	2016	972	654	668
ABC	XXX	2012	1	2016	972	654	671
ABC	XXX	2012	1	2016	972	654	1031
ABC	XXX	2012	1	2016	321	789	658
ABC	XXX	2012	1	2016	321	789	667
ABC	XXX	2012	1	2016	321	789	668
ABC	XXX	2012	1	2016	321	789	671
ABC	XXX	2012	1	2016	321	789	672
ABC	XXX	2012	1	2016	321	789	1031
ABC	XXX	2012	1	2016	321	789	1032
ABC	XXX	2012	1	2016	321	789	1072

I need to get the below table from the above one automatically.

TABLE 1
MONTH	TOTAL NET SCORE
1	1+67+432+654+789
2	SIMILARLY ALL DISTINCT VALUE
3	SIMILARLY ALL DISTINCT VALUE

Not sure where I am missing. But I have a very long data table consisting of various years and months...

I am unsure how to write the DAX function to create the second table from the first one.

[v-shex-msft · Accepted solution]

Hi @fahadfarooqi,

You can use SUMMARIZE, VALUES and CONCATENATEX functions to work through your issue:

Table = SUMMARIZE('Sample Data',[MONTH],"Value",CONCATENATEX(VALUES('Sample Data'[NET SCORE]),[NET SCORE],"+"))

Update: modify the formula to calculate the total value.

Table = SUMMARIZE('Sample',[MONTH],"Total",SUMX(VALUES('Sample'[NET SCORE]),[NET SCORE]))

Regards,

Xiaoxin Sheng

[Oxenskiold]

Just for months regardless of years

ADDCOLUMNS (
    VALUES ( theTable[Month] ),
    "Total Net Score", CALCULATE ( SUMX ( DISTINCT ( theTable[Net Score] ), theTable[Net Score] ) )
)

OR if you want month/year ....

ADDCOLUMNS (
    SUMMARIZE ( theTable, TheTable[YEAR], theTable[Month] ),
    "Total Net Score", CALCULATE ( SUMX ( DISTINCT ( theTable[Net Score] ), theTable[Net Score] ) )
)

[Anonymous]

=SUMX(
             SUMMARIZE(MyTable, MyTable[Month], MyTable[Net Score]),
             CALCULATE ( MIN ( MyTable[Net Score] ) )
   )

I would try the above, ... understanding that MIN/MAX/AVG/WHATEVER shouldn't matter, there is only 1 value anyway...

[fahadfarooqi]

Appreciate your response,

I have added same data for month 2 just to check if its working.

The expression is giving me sum of all distinct values for months consolidated.

It should have been 1+67+432+654+789 = 1943 for the first month.

Its giving me 3886 instead of 1943.

[fahadfarooqi]

@v-chuncz-msft Have a look at this one.. I am trying to figure out DAX expression to get the table down below from the first one.