Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Count and group how many time occurs

Hello, How to handle following problem. I have a dataset and I have to count how many times the answer OK apears per each REL number and then count how many times occure each value. So as a result ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    HI Anonymous,

    It sounds like you want to achieve two step aggregations to summary table records.

    For this scenario, you can create a new table with category you want and white a measure formula with variable and SUMMARIZE function to apply the first level aggerate. Then you can extract current category value as conditions with iterator function COUNTX to summarized variable table records.

    formula =
VAR currVal =
    MAX ( NewTable[Value] )
VAR summary =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( 'Table' ), [Answer] = "OK" ),
        [No],
        "Occur", COUNT ( 'Table'[Quarter] )
    )
RETURN
    COUNTX ( FILTER ( summary, [Occur] = currVal ), [No] )

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
3 years ago

HI Anonymous,

It sounds like you want to achieve two step aggregations to summary table records.

For this scenario, you can create a new table with category you want and white a measure formula with variable and SUMMARIZE function to apply the first level aggerate. Then you can extract current category value as conditions with iterator function COUNTX to summarized variable table records.

formula =
VAR currVal =
    MAX ( NewTable[Value] )
VAR summary =
    SUMMARIZE (
        FILTER ( ALLSELECTED ( 'Table' ), [Answer] = "OK" ),
        [No],
        "Occur", COUNT ( 'Table'[Quarter] )
    )
RETURN
    COUNTX ( FILTER ( summary, [Occur] = currVal ), [No] )

Regards,

Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Amazing, thank you! I have only removed ALLSELECTED to be able to filter based on [Quarter] on charts.