locked
Get Frequency, Max in PowerPivot RRS feed

  • 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 semi-colon ";" 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 PM
    Answerer

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! :)

    Some Random Thoughts

    Follow me on Twitter


    Wednesday, January 2, 2013 12:30 PM
    Answerer
  • 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/best-practices-using-summarize-and-addcolumns/

    hth,
    gerhard


    - www.pmOne.com -

    Wednesday, January 2, 2013 12:31 PM
    Answerer
  • 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 Top-Customer 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 PM
    Answerer
  • 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! :)

    Some Random Thoughts

    Follow me on Twitter


    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 Bagus

    Wednesday, 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 Top-Customer 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 Bagus

    Wednesday, 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 Bagus

    Wednesday, 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 Bagus

    I 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 Bagus

    Wednesday, 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 semi-colon ";" 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 PM
    Answerer
  • 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 semi-colon ";" 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 Bagus

    Wednesday, January 2, 2013 4:31 PM