Grouping and Banding in Power BI - Many criteria - DAX Patterns


#1

I want to create the following table (extract - see attached model for detail)

Order Banding Order Value # Orders #Customers
£ < 1000 £2740 3 3
£ < 3000 £34229 21 12

Order Value and # Orders both seem to be calculating correctly.
#Customers however is not. I want it to return the number of distinct customer ID’s that make up the £2,740 (for eg).
What it appears to be doing is evaluating the banding for the number of customers where their total order value falls within the band rather than looking at the orders that are falling in the band and counting how many customers appear in the filtered context.Dummy-Data-File.pbix (156.1 KB)

Help please ….


#2

Hi,

try this.
Orders in class =

CALCULATE( COUNTROWS(‘Order Intake’);

FILTER(

    VALUES('Order Intake');

        COUNTROWS(

            FILTER(

                Banding;

                'Order Intake'[Order Value]> Banding[BandMin]    &&  'Order Intake'[Order Value]<= Banding[BandMax]))

                > 0 ) )

Paul

Enterprise%20DNA%20Expert%20-%20Small


#3

Thanks. I don’t think that is going to work.

The following works for #Orders

Orders by Banding = SUMX('Order Intake',IF([Selected Order Value Metric]>=[BandMin]&&[Selected Order Value Metric]<[BandMax],[Selected # Order Type Metric],0))

but the following doesn’t work for #Customers

Customers by Banding = SUMX(Customer,IF([Selected Order Value Metric]>=[BandMin]&&[Selected Order Value Metric]<[BandMax],[Selected # Customer Type Metric]))

I think this is because it is re-doing the calculation rather than calculating the distinct customers based on the results of the #Orders by Banding metric

I don’t know how to put them together ……


#4

Hi,
I get this result with the given measure “Orders in class”:

Enterprise%20DNA%20Expert%20-%20Small


#5

Thanks but Both Orders in Banding and Customers in banding should 3 for the 1st band. I believe the Orders in Banding metric is correct. It is the Customer one that isn’t


#6

Is what I’m trying to do possible?


#7

I completed a quick check of some numbers here and the below returns three only when you change from <= to just <

Orders in class = 
CALCULATE( COUNTROWS('Order Intake'),
    FILTER(
        VALUES('Order Intake'),
            COUNTROWS(
                FILTER( Banding,
                    'Order Intake'[Order Value] > Banding[BandMin] 
                        &&  'Order Intake'[Order Value] <= Banding[BandMax]) ) > 0 ) )

I worked it out just completing some basic auditing of the numbers. See below

image

But you would have to work out where you want the ‘equal to’ sign here. I added it below

image

See below for customers formula

Customers in class = 
CALCULATE( DISTINCTCOUNT( Customer[CUST. ID] ),
    FILTER(
        VALUES('Order Intake'),
            COUNTROWS(
                FILTER( Banding,
                    'Order Intake'[Order Value] >= Banding[BandMin] 
                        &&  'Order Intake'[Order Value] < Banding[BandMax]) ) > 0 ) )

Definitely look to use the above technique for banding and grouping. This is the best formula methodology.

See below for more examples on this


#8

Thanks

My Orders by Banding syntax:

This I believe caters for the scenario where an order can have multiple lines and it is the addition of these lines that determines whether it falls within a particular band.

How does the COUNTROWS syntax achieve this?

Thanks for your help so far


#9

You don’t seem to be using the example I’ve worked up for you?

I’m attempting to assist you here but you don’t seem to take the suggestions. And I spent quite some time answering this for you.

COUNTROWS will account for all the orders, even if there is multiple.

Thanks


#10

I’m trying to understand. I thought Countrows just counted rows

If the table has
Order 10000 line 1 value £500
Order 10000 line 2 value £600
Order 20000 line 1 value £400
Order 20000 line 2 value £300

Won’t your syntax for number of orders less than £1000 return 4 for the above scenario whereas mine would return 1, ie
Order 10000 total value £1100 therefore excluded
Order 20000 total value £700 therefore included