Forum Discussion

theitguy's avatar
theitguy
Icon for Helper I rankHelper I
8 years ago

Group By Won't Work - How to aggregate?

Hi guys,   What I have: Learning Sessions: Participant_ID Unique_Test_ID Single_Session_ID Launch_Date Seconds_Spend Status Score 1 t_01 20984 17-11-2017 444 incomplete 0 1 ...
Need Help
v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi theitguy,

 

  • Measure1: For each test of a participant, I want the sum of [Seconds_Spend], e.g. for participant "1" and test with id "t_01" I want "591"
  • Measure2: For each test of a participant, I want the last entry of [Score], e.g. for participant "1" and test with id "t_01" I want to get "12" 

I am not sure how to determine the "last entry of [Score]", so in my test, I added an Index column in Query Editor mode, each time, I returned the last row (max index number) per Participant_ID per Unique_Test_ID.

 

Please refer to below measure formulas:

Measure1 =
CALCULATE (
    SUM ( 'Learning Sessions'[Seconds_Spend] ),
    ALLEXCEPT (
        'Learning Sessions',
        'Learning Sessions'[Participant_ID],
        'Learning Sessions'[Unique_Test_ID]
    )
)

Measure2 =
CALCULATE (
    SUM ( 'Learning Sessions'[Score] ),
    FILTER (
        'Learning Sessions',
        'Learning Sessions'[Index]
            = CALCULATE (
                MAX ( 'Learning Sessions'[Index] ),
                ALLEXCEPT (
                    'Learning Sessions',
                    'Learning Sessions'[Participant_ID],
                    'Learning Sessions'[Unique_Test_ID]
                )
            )
    )
)

 

Best regards,

Yuliana Gu

theitguy's avatar
theitguy
Icon for Helper I rankHelper I
8 years ago

 Hi Yuliana v-yulgu-msft,

 

thank you very much!

The last row for [Score] is the max of [Single_Session_ID]. ;)

I have got further questions to your solutions.

  • Can I filter the outcome of Measure2, for example only scores higher than 50 and those count those?
  • Can I create the average of the outcome of measure 1 afterwards?
  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi theitguy,

     

    • Can I filter the outcome of Measure2, for example only scores higher than 50 and those count those?

    Do you want the table visual only show records where Measure2 is greater than 50? If so, you could apply the visual level filter as below:

     

     


    • Can I create the average of the outcome of measure 1 afterwards?

    Do you want to get the average outcome per Participant_ID per Unique_Test_ID? If so, please modify the formula for Measure1:

    Measure1 =
DIVIDE (
    CALCULATE (
        SUM ( 'Learning Sessions'[Seconds_Spend] ),
        ALLEXCEPT (
            'Learning Sessions',
            'Learning Sessions'[Participant_ID],
            'Learning Sessions'[Unique_Test_ID]
        )
    ),
    CALCULATE (
        COUNTROWS ( 'Learning Sessions' ),
        ALLEXCEPT (
            'Learning Sessions',
            'Learning Sessions'[Participant_ID],
            'Learning Sessions'[Unique_Test_ID]
        )
    )
)

     

    Best regards,

    Yuliana Gu

    • theitguy's avatar
      theitguy
      Icon for Helper I rankHelper I
      8 years ago

      Hi v-yulgu-msft,

       

      thank you very much! But I am sorry, I think you got me wrong.:

       

      • Can I create the average of the outcome of measure 1 afterwards?

      What I wanted to achieve was something like 

      (197+974+439+1+78+201)/6. Summing up the aggregated tests sums and divive them bei the number of distinct test_IDs. So that I would actually get one number as an output afterwards

       

      Same goes for 

      • Can I filter the outcome of Measure2, for example only scores higher than 50 and those count those?

      I would like to count only the testIDs where the score is higher than 50. Therefore,  I would expect to see "2" as a result, in a card visualization for example.