Forum Discussion

batranikhil's avatar
batranikhil
Frequent Visitor
3 years ago

COUNTIF on a New Measure

Hi, 

 

I'm having challenge with counting the number of rows, when a New Measure is over a certain limit. 

e.g. I have a table with multiple transactions for multiple people in terms of Amounts Deposited and Withdrawn like follows:

NameAmount DepositedAmount Withdrawn
James078
Max1000
Struss056
Phil120
James032
Max340
Struss045
Phil570
James340
Max085
Struss220
Phil022
James330
Max034
Struss880
Phil044

 

I have created a 'New Measure' in Power BI Report, to give me the 'percentage of Amount Withdrawn/Amount Deposited' for each "Name". I have been able to succesfully do that. 

 

Now I want to Count IF this new measure, 'percentage of Amount Withdrawn/Amount Deposited', is over 100%. And have one of the Visuals such as 'Card' show that on the power BI report. My report in production has over a 100k rows with over 10k unique users. 

What formula, DAX query can I use to create this new Measure? or some other workaround. 

 

Cheers,

Nikhil

 

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    batranikhil , Please refer these measures

     

    % Withdrawn = Divide(Sum(Table[Amount Withdrawn]), Sum(Table[Amount Deposited]))

     

    % GT 100 = countx(Values(Table[Name]), if([% Withdrawn] >1 , [Name], blank() ) )

    • batranikhil's avatar
      batranikhil
      Frequent Visitor
      3 years ago

      Thanks Amit - worked beautifully. 

       

      Can you also help with what to use if I were to build a pie chart depicting 5 groups:

      above 200%, 100-200%, 76-100%, 50-75%, and less than 50%. 

      Any direction would help. 

       

      Cheers,

      Nikhil