cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Evaluation of data

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
5 REPLIES 5
Highlighted
Frequent Visitor

@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.

Highlighted
Memorable Member

=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...

Highlighted
Frequent Visitor

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.

Highlighted

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] ) )
)```

Highlighted
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors