Forum Discussion

Elisa112's avatar
Elisa112
Helper V
1 year ago

Summarising tables without aggregation

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

 

Need Help

4 Replies

  • dharmendars007's avatar
    dharmendars007
    Memorable Member
    1 year ago

    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's avatar
      Elisa112
      Helper V
      1 year ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Elisa112
      Helper V
      1 year ago

      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