Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count Unique count for columns in Table Visual in powerBI

I want to display the sum of all the positive values in the Margin column on the Card and the negative values in the Margin Column on another Card. First Card is 9 and the second card is 4 and third card 8

 

Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    If you want to calculate distinct count of Acct of each Product, please try:

    Distinct Acct = CALCULATE(DISTINCTCOUNT('Table'[Acct]),ALLEXCEPT('Table','Table'[Product]))
    Count of Positive Total_Margin = CALCULATE(DISTINCTCOUNT('Table'[Acct]),FILTER('Table',[Product]=MAX('Table'[Product]) && 'Table'[Total_Margin]>0))
    Count of Negative Total_Margin = CALCULATE(COUNTROWS('Table'),FILTER('Table',[Product]=MAX('Table'[Product]) && 'Table'[Total_Margin]<0))

    Output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Jbrunson09's avatar
    Jbrunson09
    Frequent Visitor
    4 years ago

    Something like this should work:

     

    Distinct Count = DISTINCTCOUNT('Table Name'[ACCT])

     

    Distinct Count Negative Values = CALCULATE(DISTINCTCOUNT('Table Name'[ACCT]),FILTER('Table Name, [TOTAL MARGIN] < 0))

     

    Distinct Count Positive Values = CALCULATE(DISTINCTCOUNT('Table Name'[ACCT]),FILTER('Table Name, [TOTAL MARGIN] >= 0))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      This works fine but what I failed to put in the question is that the margin in each is an aggregation of transactions that is the group by account number with the same product. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    If you want to calculate distinct count of Acct of each Product, please try:

    Distinct Acct = CALCULATE(DISTINCTCOUNT('Table'[Acct]),ALLEXCEPT('Table','Table'[Product]))
    Count of Positive Total_Margin = CALCULATE(DISTINCTCOUNT('Table'[Acct]),FILTER('Table',[Product]=MAX('Table'[Product]) && 'Table'[Total_Margin]>0))
    Count of Negative Total_Margin = CALCULATE(COUNTROWS('Table'),FILTER('Table',[Product]=MAX('Table'[Product]) && 'Table'[Total_Margin]<0))

    Output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.