Answered by:
Get Frequency, Max in PowerPivot
Question

Hi,
I have some issues about powerpivot to find out the expected result. Somebody can help me to solve my issue ?
Here is the example of transaction data :
Customer TransactionNo TransactionAmount TransactionDate A 1 100 1/1/2013 B 2 200 1/10/2013 A 3 150 2/1/2013 A 4 200 3/1/2013 B 5 300 3/6/2013 C 6 120 3/7/2013 A 7 300 4/1/2013 A 8 130 4/2/2013 B 9 150 5/1/2013 A 10 200 5/9/2013 A 11 100 6/1/2013 And this is expected result from above :
Highest Frequency Top Spending Amount Customer Spending > 200 Frequency > 200 7 300 2 2 Highest Frequency > Highest frequency from Customer Transaction (refer to Customer A)
Top Spending Amount > Highest Spending Amount
Customer Spending > 200 > Count of Customer who spent their money > 200 (Sum TransactionAmount > A&B)
Frequency > 200 > Count of Frequency from Transaction who spent > 200 for each transaction.
I can figured it out when I use SQL Query, but I can't do that in PowerPivot :(
Wednesday, January 2, 2013 11:32 AM
Answers

ok, these should give you the desired result:
Top Spending Amount v2:=MAXX(VALUES(Transactions[Customer]),
CALCULATE(SUM(Transactions[TransactionAmount]))) Customer Spending > 200 v2:=COUNTROWS(FILTER(SUMMARIZE(Transactions, Transactions[Customer],
"TotalAmt", SUM(Transactions[TransactionAmount])), [TotalAmt]>200)) Customer Spending > 200 v3:=COUNTROWS(FILTER(VALUES(Transactions[Customer]),
CALCULATE(SUM(Transactions[TransactionAmount]))>200))I have german Excel installed and there the semicolon ";" is used to seperate parameters  it seems as i haven't replaced all of them before posting the calculations here :)
hth,
gerhard www.pmOne.com 
 Marked as answer by Riska Bagus Wednesday, January 2, 2013 4:31 PM
Wednesday, January 2, 2013 4:24 PMAnswerer
All replies

MaxFrequency:=maxx(values(Table1[Customer]), calculate(countrows(Table1))) MaxSpendingAmount:=max(Table1[TransactionAmount]) Customer Spending More Than 200:=calculate(distinctcount(Table1[Customer]), Table1[TransactionAmount] > 200) Frequency greater than 200:=calculate(countrows(Table1), filter(Table1,Table1[MaxSpendingAmount] > 200))
Hope this helps
Cheers,
Jason  www.SqlJason.com
P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :) Edited by Jason Tom ThomasEditor Wednesday, January 2, 2013 12:31 PM
 Proposed as answer by Gerhard BruecklMVP, Editor Wednesday, January 2, 2013 1:41 PM
Wednesday, January 2, 2013 12:30 PMAnswerer 
hi, try this calculations:
Highest Frequency:=MAXX(TOPN(1, SUMMARIZE(Transactions, Transactions[Customer],
"Cnt", COUNTROWS(Transactions)), [Cnt]), [Cnt]) Top Spending Amount:=MAX([TransactionAmount]) Customer Spending > 200:=COUNTROWS(FILTER(Transactions, Transactions[TransactionAmount] > 200)) Frequency > 200:=COUNTROWS(FILTER(SUMMARIZE(Transactions, Transactions[Customer],
"TranAmt", SUM(Transactions[TransactionAmount])), [TranAmt] > 200))both, [Highest Frequency] and [Frequency > 200] can be further advanced using this technique:
http://www.sqlbi.com/articles/bestpracticesusingsummarizeandaddcolumns/hth,
gerhard www.pmOne.com 
Wednesday, January 2, 2013 12:31 PMAnswerer 
Jason's calculations are much more readable, produce the same results and are also very likely to perform better
the reason why I used SUMMARIZE() is because it also allows you to get the Name of the TopCustomer with the highest frequency very easily:
Highest Frequency Customer:=CALCULATE(VALUES(Transactions[Customer]),TOPN(1;SUMMARIZE(Transactions, Transactions[Customer], "Cnt"; COUNTROWS(Transactions)), [Cnt]))
 www.pmOne.com 
Wednesday, January 2, 2013 1:41 PMAnswerer 
MaxFrequency:=maxx(values(Table1[Customer]), calculate(countrows(Table1))) MaxSpendingAmount:=max(Table1[TransactionAmount]) Customer Spending More Than 200:=calculate(distinctcount(Table1[Customer]), Table1[TransactionAmount] > 200) Frequency greater than 200:=calculate(countrows(Table1), filter(Table1,Table1[MaxSpendingAmount] > 200))
Hope this helps
Cheers,
Jason  www.SqlJason.com
P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
I have tried your function, and this is the result :
MaxFrequency : it works.
MaxSpendingAmount : it works.
CustomerSpendingMoreThan200 : "This is not a valid DAX expression." I thought it is because distinctcount function. (FYI, I am using Ms. Excel 2010). any idea ?
Frequency Greater Than 200 : It works :)
regards,
Riska BagusWednesday, January 2, 2013 3:02 PM 
Jason's calculations are much more readable, produce the same results and are also very likely to perform better
the reason why I used SUMMARIZE() is because it also allows you to get the Name of the TopCustomer with the highest frequency very easily:
Highest Frequency Customer:=CALCULATE(VALUES(Transactions[Customer]),TOPN(1;SUMMARIZE(Transactions, Transactions[Customer], "Cnt"; COUNTROWS(Transactions)), [Cnt]))
 www.pmOne.com 
Hi Gerhard,
Honestly this is what I wanted to be shown in the report. I would like to show Highest Customer Spending Amount. But you calculation does not work. This is the result from PowerPivot : The Syntax ";" is incorrect. And I thought "SUMMARIZE" is not available for Ms. Excel 2010.
Thanks,
Riska BagusWednesday, January 2, 2013 3:12 PM 
I would like to give more detail information.
 Top Spending Amount : It is total of customer spending (not for each transaction). For the example above, top spending amount must be 1080 (it comes from Total Spending for each customer, and put the highest value into the report). In SQL, we can figured it out like this :
"select top 1 SUM(TransactionAmount)
from Transaction
having sum(TransactionAmount)>200
order by TransactionAmount desc"
 Customer Spending > 200 : it describe total of customer spending also. And count how many customer who has been spent their money more than 200. In this case, A has been spent 1080, and B has been spent 680, and C 120. It means, the report will be shown 2 (A & B)
I appreciate for Jason and Gerhard who helped me for fixed 2 column (y), But I still need to solve 2 case mentioned above (Top Spending Amount & Customer Spending)
Many Thanks,
Riska BagusWednesday, January 2, 2013 3:45 PM 
I would like to give more detail information.
 Top Spending Amount : It is total of customer spending (not for each transaction). For the example above, top spending amount must be 1080 (it comes from Total Spending for each customer, and put the highest value into the report). In SQL, we can figured it out like this :
"select top 1 SUM(TransactionAmount)
from Transaction
having sum(TransactionAmount)>200
order by TransactionAmount desc"
 Customer Spending > 200 : it describe total of customer spending also. And count how many customer who has been spent their money more than 200. In this case, A has been spent 1080, and B has been spent 680, and C 120. It means, the report will be shown 2 (A & B)
I appreciate for Jason and Gerhard who helped me for fixed 2 column (y), But I still need to solve 2 case mentioned above (Top Spending Amount & Customer Spending)
Many Thanks,
Riska BagusI solved top spending amount using : =maxx(values(TRANSHDR[CustomerID]), calculate(SUM(TRANSHDR[TransAmt])))
currently still looking for helps to solve customer spending > 200 :)
warm regards,
Riska BagusWednesday, January 2, 2013 4:21 PM 
ok, these should give you the desired result:
Top Spending Amount v2:=MAXX(VALUES(Transactions[Customer]),
CALCULATE(SUM(Transactions[TransactionAmount]))) Customer Spending > 200 v2:=COUNTROWS(FILTER(SUMMARIZE(Transactions, Transactions[Customer],
"TotalAmt", SUM(Transactions[TransactionAmount])), [TotalAmt]>200)) Customer Spending > 200 v3:=COUNTROWS(FILTER(VALUES(Transactions[Customer]),
CALCULATE(SUM(Transactions[TransactionAmount]))>200))I have german Excel installed and there the semicolon ";" is used to seperate parameters  it seems as i haven't replaced all of them before posting the calculations here :)
hth,
gerhard www.pmOne.com 
 Marked as answer by Riska Bagus Wednesday, January 2, 2013 4:31 PM
Wednesday, January 2, 2013 4:24 PMAnswerer 
ok, these should give you the desired result:
Top Spending Amount v2:=MAXX(VALUES(Transactions[Customer]),
CALCULATE(SUM(Transactions[TransactionAmount]))) Customer Spending > 200 v2:=COUNTROWS(FILTER(SUMMARIZE(Transactions, Transactions[Customer],
"TotalAmt", SUM(Transactions[TransactionAmount])), [TotalAmt]>200)) Customer Spending > 200 v3:=COUNTROWS(FILTER(VALUES(Transactions[Customer]),
CALCULATE(SUM(Transactions[TransactionAmount]))>200))I have german Excel installed and there the semicolon ";" is used to seperate parameters  it seems as i haven't replaced all of them before posting the calculations here :)
hth,
gerhard
 www.pmOne.com 
Wow, Thank you Gerhard. I used v3, because my PowerPivot doesn't know SUMMARIZE :D.
It works now :).
BR,
Riska BagusWednesday, January 2, 2013 4:31 PM