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:

DescAmount
A10
B4
C5
D3
C2

 

result :

desc<=5>5
a 1
b1 
c2 
d1 

 

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.

4 Replies

  • danextian's avatar
    danextian
    Super 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      It doesn't works...

      Let me clarify , I've 50 million rows so i have summarized table in PBI, you can see sample below.

      I need classifcation <5, >5 based on description count & sales.

       

      we need the result based on the input 'summarize table' . suggest on DAX

       

      tried measurement

      Test = COUNTAX('table',[amount]>5)

      desc <5 = IF([amount]<5,[desc])    //description count
      desc >5 = IF([amount]>5,[desc])     //description count

      example : sum table in PBI

      DescAmount
      A10
      B4
      C5
      D3
      C2

       

      result :

      desc<=5>5
      a 1
      b1 
      c2 
      d1

       

       

       

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    1 year ago

    Anonymous 

    you can try this

     

    >5 = CALCULATE(COUNTROWS('Table'),'Table'[Amount]>5)
     
    measure = CALCULATE(COUNTROWS('Table'),'Table'[Amount]<=5)
     
    then update the measure name to <=5
  • v-achippa's avatar
    v-achippa
    Community Support
    1 year ago

    Hi Anonymous,

     

    Thank you for reaching out to Microsoft Fabric Community.

     

    Thank you danextian and ryan_mayu for the prompt response.

     

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by ryan resolved your issue? or let us know if you need any further assistance.
    If any response resolved your issue, please mark it as "Accept as solution" and give a kudos if you found it helpful.

     

    Thanks and regards,

    Anjan Kumar Chippa