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

DAX DAX Calculations
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”:

DummyData
DummyData764×256 58.1 KB

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

image
image731×221 6.78 KB

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

11

Ok if that is the logic you are looking for you will need to make some small adjustments to the formula

Understanding context is absolutely key here (outside and inside the formula)

First if you need the unique orders you will want to be using DISTINCTCOUNT instead of COUNTROWS. Then you will also want to reference the exact order ID columns.

You will also probably want to create a measure called Total Order Value (or something like this). This calculation will internally within the formula aggregate up the total amount for each order rather than just at each seperate line.

You’ll the pattern though is exactly the same

See example below.

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

This should do it.

I haven’t been able to test it, but logically this should work.

1 Like