cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Evaluation of data

I have a sample data table to understand a problem.

 

PLAYER NAMETEAMYEAR JOINEDMONTHYEARGROSS SCORENET SCORECategory
ABCXXX2012120164441649
ABCXXX2012120164441658
ABCXXX2012120164441668
ABCXXX2012120164441671
ABCXXX20121201644411031
ABCXXX20121201623467594
ABCXXX20121201623467625
ABCXXX20121201623467649
ABCXXX20121201623467658
ABCXXX20121201623467671
ABCXXX201212016234671031
ABCXXX201212016234671045
ABCXXX201212016721432658
ABCXXX201212016721432671
ABCXXX2012120167214321031
ABCXXX201212016972654625
ABCXXX201212016972654658
ABCXXX201212016972654668
ABCXXX201212016972654671
ABCXXX2012120169726541031
ABCXXX201212016321789658
ABCXXX201212016321789667
ABCXXX201212016321789668
ABCXXX201212016321789671
ABCXXX201212016321789672
ABCXXX2012120163217891031
ABCXXX2012120163217891032
ABCXXX2012120163217891072

 

 

 

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

TABLE 1
MONTHTOTAL NET SCORE
11+67+432+654+789
2SIMILARLY ALL DISTINCT VALUE
3SIMILARLY 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
Community Support

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],"+"))

 

2.PNG

 

Update: modify the formula to calculate the total value.

 

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

8.PNG

 

 

Regards,

Xiaoxin Sheng

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

View solution in original post

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

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.

 

Capture.PNG

Highlighted
Advocate I
Advocate I

 

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
Community Support

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],"+"))

 

2.PNG

 

Update: modify the formula to calculate the total value.

 

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

8.PNG

 

 

Regards,

Xiaoxin Sheng

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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