Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Name | Amount Deposited | Amount Withdrawn |
James | 0 | 78 |
Max | 100 | 0 |
Struss | 0 | 56 |
Phil | 12 | 0 |
James | 0 | 32 |
Max | 34 | 0 |
Struss | 0 | 45 |
Phil | 57 | 0 |
James | 34 | 0 |
Max | 0 | 85 |
Struss | 22 | 0 |
Phil | 0 | 22 |
James | 33 | 0 |
Max | 0 | 34 |
Struss | 88 | 0 |
Phil | 0 | 44 |
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
@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() ) )
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
User | Count |
---|---|
94 | |
87 | |
79 | |
76 | |
69 |
User | Count |
---|---|
116 | |
107 | |
88 | |
64 | |
63 |