locked
[DAX] Summarize by summarize table RRS feed

  • Question

  • Hi All,

    Could it be possible to use DAX with SUMMARIZE(SUMMARIZE(....)) query?

    =====================

    Fact table looks like: (per customer, they only have same value per each month, it's not related to order)

    Month     CustomerID OrderNum Order Type Value
    2015/1 CUST001 S000001 New 1000
    2015/1 CUST001 S000002 Upgrade 1000
    2015/2 CUST001 S000003 New 50
    2015/3 CUST001 S000004 Renew 25000
    2015/3 CUST001 S000005 Renew 25000


    Dimension table:

    Value Range

    0 - 1000

    1001 - 10000

    10001 - 50000

    50001 +

    =====================

    And I would like to create a measure to find out"How many orders by each value range (by customer) ?"

    by Month view

    Month     Value Range Order count
    2015/1 0 - 1000  2
    2015/2 0 - 1000 1
    2015/3 10001 - 50000 2

    (e.g. 2015/1: customer avg amount will be 1000)

    ============

    But in Quarter view

    Quarter Value Range Order count
    2015Q1 1001 - 10000 5

    (e.g. 2015/Q1: customer avg amount will be (1000+50+25000)/3 = 8683)

    ===================================================

    So far I didn't find good way to fulfill with this, and here is my thought:

    1st, use SUMMARIZE(Fact, Fact[CustomerID],Fact[Value]) to distinct table and get only 1 value per customer

    2nd, use SUMMARIZE again to find out how many order created by customer, SUMMARIZE(Fact, Fact[CustomerID],"Order_count",DISTINCTCOUNT(Fact[OrderNum])) 

    3rd, combine above 2 result and get Month/Quarter view like above


    I'm just wondering if you could advice any good idea to handle this scenario, thanks in advance.

    Best Regards,

    Jackie


    • Edited by Jackie_tw Sunday, September 6, 2015 3:09 AM
    Sunday, September 6, 2015 3:03 AM

Answers

  • Hi Jackie,

    I never really understood what to use SUMMARIZE for, but it's not your scenario :-)

    Extend your Value Range table with two columns, Minimum and Number:

    Value Range           Minimum         Number

    0 - 1000                 0                    1

    1001 - 10000         1001               2

    10000 - 50000       10000              3

    50000+                 50001              4

    Now, create a calculated column, say Range, in your fact table with:

    = COUNTROWS(FILTER(ValueRange,ValueRange[Minimum]<[Value]))

    Next, create a relationship from Fact[Range] to ValueRange[Number]. Use the Value Range column as a row label in a pivot table.

    • Proposed as answer by Charlie Liao Friday, September 18, 2015 2:01 AM
    • Marked as answer by Charlie Liao Tuesday, September 22, 2015 8:55 AM
    Tuesday, September 15, 2015 3:36 PM
    Answerer