Forum Discussion

brissae's avatar
brissae
Frequent Visitor
10 years ago
Solved

Count occurencies

Hello everyone,  I have thw following information:       And a want to get the next grid:     The idea is to show the count of people who resolved more than 10 problems related to ...
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    brissae

     

    You need two calculated tables.

     

     

    Aggregate Table =
SUMMARIZE (
    'YourTable',
    YourTable[Product name],
    YourTable[OWNER],
    "is cnt >=10", IF ( COUNTA ( 'YourTable'[OWNER] ) >= 10, 1, 0 ),
    "is cnt<10", IF ( COUNTA ( 'YourTable'[OWNER] ) >= 10, 0, 1 )
)

     

    grid =
SUMMARIZE (
    'Aggregate Table',
    'Aggregate Table'[Product name],
    "People>=10", SUM ( 'Aggregate Table'[is cnt >=10] ),
    "People<10", SUM ( 'Aggregate Table'[is cnt<10] )
)

     

    Regarding average, I don't see any date column in your table.

     

Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
10 years ago

brissae

 

You need two calculated tables.

 

 

Aggregate Table =
SUMMARIZE (
    'YourTable',
    YourTable[Product name],
    YourTable[OWNER],
    "is cnt >=10", IF ( COUNTA ( 'YourTable'[OWNER] ) >= 10, 1, 0 ),
    "is cnt<10", IF ( COUNTA ( 'YourTable'[OWNER] ) >= 10, 0, 1 )
)

 

grid =
SUMMARIZE (
    'Aggregate Table',
    'Aggregate Table'[Product name],
    "People>=10", SUM ( 'Aggregate Table'[is cnt >=10] ),
    "People<10", SUM ( 'Aggregate Table'[is cnt<10] )
)

 

Regarding average, I don't see any date column in your table.

 

  • brissae's avatar
    brissae
    Frequent Visitor
    10 years ago

    Thank you Eric_Zhang  Your answer helped a lot, I could finish my report, it was more complex that I thought.

    You are brilliant!!!

  • brissae's avatar
    brissae
    Frequent Visitor
    10 years ago

    Eric_Zhang Hello again, how can I do to show this results by month and year? As filters. 

    I made it work, but everytime I select more than 1 month, the numbers go incorrect. I don't have a date table, I only have the dates associated with every row . I tried to use this but it didn't work, I don't kno how to use this time intelligence funct.

     

    Hope you have an advice, thank you.