Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Count based on revenue amount

I need 3 measure like >5, <5, result basically we need to create >5 how many distinct count of desc based on amount criteria, suggest example table: Desc Amount A 10 B 4 C 5 D ...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    Hi Anonymous 

     

     

    Assuming Amount is a physical column in your table, the measure would be

    CALCULATE (
    DISTINCTCOUNT ( 'table'[desc] ),
    keepfilers ( 'table'[amount] ) <= 5
)

    But youre expected result is confusing thouugh. I thought you wanted the distinct count which means that each value in desc should only be counted once - C has 2. If you mean to say the count of records, use:

    CALCULATE (
    COUNTROWS ( 'table' ),
    keepfilers ( 'table'[amount] ) <= 5
)

    Just change <= to  > for a different calculation.

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

Hi Anonymous 

 

 

Assuming Amount is a physical column in your table, the measure would be

CALCULATE (
    DISTINCTCOUNT ( 'table'[desc] ),
    keepfilers ( 'table'[amount] ) <= 5
)

But youre expected result is confusing thouugh. I thought you wanted the distinct count which means that each value in desc should only be counted once - C has 2. If you mean to say the count of records, use:

CALCULATE (
    COUNTROWS ( 'table' ),
    keepfilers ( 'table'[amount] ) <= 5
)

Just change <= to  > for a different calculation.