Summarising tables without aggregation

[Elisa112]

Hello Experts

I am trying to summarise a student assessment table without aggregating the scores.  I want to compare the difference (improved/declined) in another column but first of all I have a problem summrising the columns without power BI aggregating the scores. 

I have tried this to summarise the table

Assessment Summary = ADDCOLUMNS( ADDCOLUMNS(SUMMARIZE
('vBIStudent_Assessment','vBIStudent_Assessment'[Student ID], vBIstudent_Assessment[Assessment ID]),
"First Assessment Meeting",MINX(FILTER('vBIStudent_Assessment','vBIStudent_Assessment'[Student ID]=EARLIER('vBIStudent_Assessment'[Student ID])
&& 'vBIStudent'[Assessment Type]="Beginner" && 'vBIStudent_Assessment'[Assessment Stage]="First Assessment Meeting"),
'vBIStudent_Assessment'[Assessment Value (IS Score)]),
"First Assessment Review",MINX(FILTER('vBIStudent_Assessment','vBIStudent_Assessment'[Student ID]=EARLIER('vBIStudent_Assessment'[Student ID])
&& 'vBIStudent_Assessment'[Assessment Type]="Beginner" && 'vBIStudent_Assessment'[Assessment Stage]="First Review Meeting"),
'vBIRefugee_Assessment'[Assessment Value (IS Score)],
))

but I get a minx error :

the solutions should look something like this

Student ID   Assessment ID     Date    First Meeting       First Review      Difference +/-   Second Meeting...

100               200                     1 Jan      25                        30                       5

All guidance much appreciated

thank

[Anonymous]

Hi, @Elisa112 

Based on your information, I create sample table:

Then create measures:

First Assessment Meeting = 
CALCULATE(
    MIN('Table'[Assessment Value (IS Score)]),
    'Table'[Assessment Type] = "Beginner" &&
    'Table'[Assessment Stage] = "First Assessment Meeting"
)

First Assessment Review = 
CALCULATE(
    MIN('Table'[Assessment Value (IS Score)]),
    'Table'[Assessment Type] = "Beginner" &&
    'Table'[Assessment Stage] = "First Review Meeting"
)

Difference +/- = 
[First Assessment Review] - [First Assessment Meeting]

Put this measure in table view:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Elisa112]

Thanks, but I need to create a summary table before i create the measures and this is what I have an issue with

any ideas?

thank  you

[dharmendars007]

Hello @Elisa112 , 

Using CALCULATE Instead of MINX:

1.CALCULATE is better suited for this case since it allows filtering the table directly.

2.Each CALCULATE block fetches the minimum score for specific assessment stages.

Assessment Summary =
ADDCOLUMNS(
SUMMARIZE(
vBIStudent_Assessment,
vBIStudent_Assessment[Student ID],
vBIStudent_Assessment[Assessment ID]),
"First Meeting Score",
CALCULATE(
MIN(vBIStudent_Assessment[Assessment Value (IS Score)]),
vBIStudent_Assessment[Assessment Stage] = "First Assessment Meeting",
vBIStudent_Assessment[Assessment Type] = "Beginner"),
"First Review Score",
CALCULATE(
MIN(vBIStudent_Assessment[Assessment Value (IS Score)]),
vBIStudent_Assessment[Assessment Stage] = "First Review Meeting",
vBIStudent_Assessment[Assessment Type] = "Beginner"),
"Difference",
[First Review Score] - [First Meeting Score])

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[Elisa112]

Thank you @dharmendars007 

but I am getting this error:

This is the name of the added columns I am trying to use, all is well until I get to the difference column and then the added columns are not recognised

Any idea why

thanks again