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

rocky09 yes but against maximum reqisition_event id only.

rocky09's avatar
rocky09
Solution Sage
7 years ago

I am not sure if i understand it correctly. Anyway, try to create New Table from Modeling tab and use this DAX. Make sure to replace the table name and column as per your data.

 

summarizedata = SUMMARIZE(Table1,Table1[requisition_event_id,],"Count",COUNT(Table1[requisition_id,]))
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    rocky09  hi rocky thanks i get the data like 

     

    requisition_event id     count

    28                                1896

     

    but i need bifurcation according to requisition_event_id like you can see in table that requisition_id is repeated according to event id and i need count of requisition_id wrt count of max event id