Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

summarize data

  hi , i have created a measure MaxID and drag column Requisition_id to get a clustered column visualization. the data came as:   MaxID requisition_id 1 99 4 100 5 101 4 102 ...
General Comment
Need Help
Show and Tell
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous

    From you information, MaxID should be the max requisition_event_id per requisition_id, Count of requisition_id should be the count of requisition_id per MaxID, right?

     

    in my test, [Measure] is the  MaxID, i could also create a calculated column max to replace it, then create another column count for Count of requisition_id.

    count = CALCULATE(COUNT(Sheet1[requisition_id,]),ALLEXCEPT(Sheet1,Sheet1[max]))

max = CALCULATE(MAX([requisition_event_id,]),ALLEXCEPT(Sheet1,Sheet1[requisition_id,]))

     

     

    Or if you need distintcount, you can use the following formula

    distintcount = CALCULATE(DISTINCTCOUNT(Sheet1[requisition_id,]),ALLEXCEPT(Sheet1,Sheet1[max]))

     

    Best Regards

    Maggie

     

     

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

PattemManohar

 

or you can refer the final data. its more organized. u can see requisition id is repetitive and i am trying to get only max requisiion_event_id from given requisition_id

 

id,requisition_id,requisition_event_id,upload_link,created_by,updated_by,created_at,updated_at
220991 50450407-10-2018 20:0607-10-2018 20:06
2211001 59659608-10-2018 11:3508-10-2018 11:35
2571002 1108-10-2018 20:2108-10-2018 20:21
3231004 323209-10-2018 11:2109-10-2018 11:21
2221011 59659608-10-2018 11:4008-10-2018 11:40
2561012 1108-10-2018 20:2108-10-2018 20:21
3201013 323209-10-2018 11:2009-10-2018 11:20
3211015 323209-10-2018 11:2009-10-2018 11:20
2231021 59659608-10-2018 11:4708-10-2018 11:47
2551022 1108-10-2018 20:2108-10-2018 20:21
3191024 323209-10-2018 11:2009-10-2018 11:20
2241031 41941908-10-2018 12:2508-10-2018 12:25
3411032 42442409-10-2018 16:2909-10-2018 16:29
3531033 323209-10-2018 17:2009-10-2018 17:20
3691035 60660610-10-2018 10:2010-10-2018 10:20
2251041 41941908-10-2018 12:2708-10-2018 12:27
2541042 1108-10-2018 20:2008-10-2018 20:20
3181043 323209-10-2018 11:1909-10-2018 11:19
rocky09's avatar
rocky09
Solution Sage
7 years ago

AnonymousI am bit confused. Can you post some example how do you want to see the data. I know, you have mentioned in first post. But, it will be great if you post example with the last posted data.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    rocky09 HI

     

    my main table is 

    d,requisition_id,requisition_event_id,upload_link,created_by,updated_by,created_at,updated_at
    220991 50450407-10-2018 20:0607-10-2018 20:06
    2211001 59659608-10-2018 11:3508-10-2018 11:35
    2571002 1108-10-2018 20:2108-10-2018 20:21
    3231004 323209-10-2018 11:2109-10-2018 11:21
    2221011 59659608-10-2018 11:4008-10-2018 11:40
    2561012 1108-10-2018 20:2108-10-2018 20:21
    3201013 323209-10-2018 11:2009-10-2018 11:20
    3211015 323209-10-2018 11:2009-10-2018 11:20
    2231021 59659608-10-2018 11:4708-10-2018 11:47
    2551022 1108-10-2018 20:2108-10-2018 20:21
    3191024 323209-10-2018 11:2009-10-2018 11:20
    2241031 41941908-10-2018 12:2508-10-2018 12:25
    3411032 42442409-10-2018 16:2909-10-2018 16:29
    3531033 323209-10-2018 17:2009-10-2018 17:20
    3691035 60660610-10-2018 10:2010-10-2018 10:20
    2251041 41941908-10-2018 12:2708-10-2018 12:27
    2541042 1108-10-2018 20:2008-10-2018 20:20
    3181043 323209-10-2018 11:1909-10-2018 11:19

     

    i ahve created measure from above table :  Measure name is Max ID

     MaxID = MAx('eta requisition_life_cycles'[requisition_event_id]) 

     

    then i created a visualization with measure max id and drag requisition_id in clustered column chart i get data as

    MaxIDrequisition_id
    199
    4100
    5101
    4102
    5103
    4104
    5105
    4106
    5107
    5108
    4109
    5110
    5111
    4112
    5113
    5114
    5115
    5116
    5117

     

     

    but i need data as

     

    Max IDCount of requisition_id
    135
    232
    314
    4129
    5229
    617
    7117

     

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      7 years ago

      Hi Anonymous

      From you information, MaxID should be the max requisition_event_id per requisition_id, Count of requisition_id should be the count of requisition_id per MaxID, right?

       

      in my test, [Measure] is the  MaxID, i could also create a calculated column max to replace it, then create another column count for Count of requisition_id.

      count = CALCULATE(COUNT(Sheet1[requisition_id,]),ALLEXCEPT(Sheet1,Sheet1[max]))

max = CALCULATE(MAX([requisition_event_id,]),ALLEXCEPT(Sheet1,Sheet1[requisition_id,]))

       

       

      Or if you need distintcount, you can use the following formula

      distintcount = CALCULATE(DISTINCTCOUNT(Sheet1[requisition_id,]),ALLEXCEPT(Sheet1,Sheet1[max]))

       

      Best Regards

      Maggie

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        v-juanli-msft Hi Maggie, thank you so much.......you made my day. As a new user, this is my first perfect visualization.

    • rocky09's avatar
      rocky09
      Solution Sage
      7 years ago

      still, I don't get. Are you counting requisition_ids against requisition_event_id?

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        rocky09 yes but against maximum reqisition_event id only.