[DAX] Summarize by summarize table

• 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 Sunday, September 6, 2015 3:09 AM
Sunday, September 6, 2015 3:03 AM

• 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 Friday, September 18, 2015 2:01 AM
• Marked as answer by Tuesday, September 22, 2015 8:55 AM
Tuesday, September 15, 2015 3:36 PM