Price Segments as a calculated table

DAX DAX Calculations
1

Hi Edna,
Im trying to calculate Price Segments (or the price quartiles) within each category with the following DAX as a calculated table. But it returns the same value for all the categories.

Any suggestions on how to do it per category ID since it does it for the whole table atm?

PriceSegments = 
CALCULATETABLE(
    ADDCOLUMNS (
        VALUES ( 'factSales'[CategoryID] ),
        "@Min", 0,
        "1st Quartile", TRUNC( CURRENCY ( PERCENTILEX.INC ( factSales, factSales[PRICE_UNIT_SEK], 0.20 ) ), 2),
        "2nd Quartile", TRUNC( CURRENCY ( PERCENTILEX.INC ( factSales, factSales[PRICE_UNIT_SEK], 0.40 ) ), 2),
        "3rd Quartile", TRUNC( CURRENCY ( PERCENTILEX.INC ( factSales, factSales[PRICE_UNIT_SEK], 0.60 ) ), 2),
        "4th Quartile", TRUNC( CURRENCY ( PERCENTILEX.INC ( factSales, factSales[PRICE_UNIT_SEK], 0.80 ) ), 2),
        "5th Quartile", CURRENCY ( 99999999 )
    ),
    FILTER( dimCalendar, dimCalendar[isYTD] )
)

image
image1417×667 48.5 KB

2

Hi @Tibbie, we aim to consistently improve the topics being posted on the forum.

I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Not completing your data may sometimes cause delay in getting an answer.

3

Hi @EnterpriseDNA ,
Thanks for the info regarding the suggestion of posting a PBIX file.
Here is the attached sample with the above code and set-up.

Contoso Sales Sample.pbix (23.9 MB)

4

Thanks for sending in the PBIX @Tibbie. Bumpin this post for more visibility.

5

Does it need to be explained further or is it just a hard (no fun) nut to crack?

6

@Tibbie ,

I started working this one late last night, and it proved annoyingly harder to crack than I thought. :smiley: Will continue to have at it, and hopefully have a solution back to you later today.

  • Brian
7

Thanks a lot @BrianJ

This is roughly what I later want to achieve and then be able to filter on each category. Got the inspiration from Sam in one of the courses on “dynamic banding” or a similar name :slight_smile:

image
image2006×862 118 KB

Y-Axis

Sales Per PriceSegment = 
SUMX (
    VALUES ( 'Date'[Year] ),
    VAR ProductsInSegment =
        FILTER (
            ALLSELECTED ( Product[ARTICLE] ),
            VAR SalesOfProduct = [Sales Price]
            VAR SegmentForProduct =
                FILTER (
                    'PriceSegments',
                    NOT ISBLANK ( SalesOfProduct )
                        && 'PriceSegments'[Min Price] < SalesOfProduct
                        && 'PriceSegments'[Max Price] >= SalesOfProduct
                )
            VAR IsProductInSegments = NOT ISEMPTY ( SegmentForProduct )
            RETURN
                IsProductInSegments
        )
    VAR Result =
        CALCULATE ( [Sales], KEEPFILTERS ( ProductsInSegment ) )
    RETURN
        Result
)

.
X-Axis

Sales Price Segment = 
SUMX (
    VALUES ( 'Calendar'[Year] ),
    VAR ProductsInSegment =
        FILTER (
            ALLSELECTED ( Product[ARTICLE] ),
            VAR SalesOfProduct = [Sales Price]
            VAR SegmentForProduct =
                FILTER (
                    'PriceSegments',
                    NOT ISBLANK ( SalesOfProduct )
                        && 'PriceSegments'[Min Price] < SalesOfProduct
                        && 'PriceSegments'[Max Price] >= SalesOfProduct
                )
            VAR IsProductInSegments = NOT ISEMPTY ( SegmentForProduct )
            RETURN
                IsProductInSegments
        )
    VAR Result =
        CALCULATE ( [Sales Price], KEEPFILTERS ( ProductsInSegment ) )
    RETURN
        Result
)

.

The only problem with the above measures is that they relied on a table that looked like this
image

with this dax

PriceSegments = 
UNION (
    ROW ( 
        "Price Range", "1st Quartile", 
        "Sort", INT( 1), 
        "Min Price", CURRENCY ( 0 ), 
        "Max Price", CURRENCY ( PERCENTILEX.INC ( factSales, factSales[PRICE_UNIT_SEK], 0.20 ) ) 
    ),
    ROW ( 
        "Price Range", "2nd Quartile", 
        "Sort", INT( 2), 
        "Min Price", CURRENCY ( PERCENTILEX.INC ( factSales, factSales[PRICE_UNIT_SEK], 0.20 ) ), 
        "Max Price", CURRENCY ( PERCENTILEX.INC ( factSales, factSales[PRICE_UNIT_SEK], 0.40 ) ) 
    ),
    ROW ( 
        "Price Range", "3rd Quartile", 
        "Sort", INT( 3), 
        "Min Price", CURRENCY ( PERCENTILEX.INC ( factSales, factSales[PRICE_UNIT_SEK], 0.40 ) ), 
        "Max Price", CURRENCY ( PERCENTILEX.INC ( factSales, factSales[PRICE_UNIT_SEK], 0.60 ) ) 
    ),
    ROW ( 
        "Price Range", "4th Quartile", 
        "Sort", INT( 4), 
        "Min Price", CURRENCY ( PERCENTILEX.INC ( factSales, factSales[PRICE_UNIT_SEK], 0.60 ) ), 
        "Max Price", CURRENCY ( PERCENTILEX.INC ( factSales, factSales[PRICE_UNIT_SEK], 0.80 ) ) 
    ),
    ROW ( 
        "Price Range", "5th Quartile", 
        "Sort", INT( 5), 
        "Min Price", CURRENCY ( PERCENTILEX.INC ( factSales, factSales[PRICE_UNIT_SEK], 0.80 ) ), 
        "Max Price", CURRENCY ( 99999999 ) 
    )
)

.
So I will need to think that one through as well :stuck_out_tongue:

8

@Tibbie ,

OK, with a fresh look at this, I now see you were VERY close here, and all that was needed was to wrap the Percentilex.inc statements in CALCULATE to initiate context transition.

image
image1561×794 54 KB

I hope this is helpful.

  • Brian
1 Like
9

Thanks a lot @BrianJ
Actually I was trying something similar with

VAR _category = VALUES ( ‘factSales’[CategoryID] )

and then use

CALCULATE( TRUNC( CURRENCY ( PERCENTILEX.INC ( factSales, factSales[PRICE_UNIT_SEK], 0.20 ) ), 2), ‘factSales’[CategoryID] = _category

but it did not work so your solution was much simpler :smiley:

10

@Tibbie ,

One other thing I meant to mention - if you are going to be reusing your percentilex.inc calculations in other analyses, it may make sense to pull them out as separate measures. Doing so, will also solve your problem above, since measures automatically trigger the context transition for which we had to use the CALCULATE statements.

  • Brian
11

@BrianJ ,
Okey sounds logic, but just to make sure i follow you:

Example mesure:

1st Quartile = 
    TRUNC( CURRENCY ( PERCENTILEX.INC ( Sales, Sales[UnitPrice], 0.20 ) ), 2)

PriceSegments = 
CALCULATETABLE(
    ADDCOLUMNS (
        VALUES ( 'Sales'[SubCategory] ),
        "@Min", 0,
        "1st Quartile", [1st Quartile],
        "2nd Quartile", [2nd Quartile],
        "3rd Quartile", [3rd Quartile],
        "4th Quartile", [4th Quartile],
        "5th Quartile", CURRENCY ( 999999 )
    ),
    FILTER( 'Date', 'Date'[Calendar YearNumber] = 2009 )
)
12

@Tibbie ,

Bingo!

  • Brian