Segmentation Help

[tkfisher]

Hi All,

I require some help with detailed segmentation measures. I used the link below to help guide me, but my issue is a little more complex as my "segmentation rules" differ for each item number.

https://www.daxpatterns.com/dynamic-segmentation/

I have a "Segment Rules" table called 'Price List Updated'. It is linked to the Item_Master table on Item #. I also have a 'sales' table that calculates total revenue and ASP.

What I am looking to do is bucket each item's ASP into buckets, defined by the 'Price List Updated' table. I then want to be able to create different measures that sum total revenue, total quantity sold, or total count of items. Here is my DAX:

Revenue Segmentation = SUMX(VALUES('Item_Master'[Item #]),

var ItemsInPriceLevel = FILTER(ALLSELECTED(Item_Master),

var SalesOfItem = 'sales'[Spend @ actual]

var ItemASP = sales[ASP (calculated)]

var PriceLevelOfItem = FILTER('Price List Updated',NOT ISBLANK(ItemASP) && 'Price List Updated'[Lowest Price] <= ItemASP && IF(ISBLANK('Price List Updated'[Highest Price]), 999999999999999999999999999999 > ItemASP, 'Price List Updated'[Highest Price] > ItemASP))

var IsItemInPriceLevel = NOT ISEMPTY(PriceLevelOfItem)

return IsItemInPriceLevel)

return CALCULATE(sales[Spend @ actual] ,KEEPFILTERS(ItemsInPriceLevel)))

The result is close, but you can see that the totals are too high in each category and don't even match the total in my table. 

Any help would be very appreciated!

[tkfisher]

I have a .pbix file that replicates my issue, what is the best way to share it on this forum?

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[tkfisher · Accepted solution]

Got it! As normal, was overcomplicating the DAX needed. Here is the corrected code:

Revenue Segmentation =

SUMX (

VALUES ( 'Price List Updated'[Item Number] ),

VAR ItemsInPriceLevel =

CALCULATETABLE (

FILTER (

'Price List Updated',

VAR SalesOfItem = [Spend @ actual]

VAR ItemASP = [ASP (calculated)]

VAR IsItemInPriceLevel =

NOT ISBLANK ( ItemASP ) && 'Price List Updated'[Lowest Price] <= ItemASP

&& ItemASP < COALESCE ( 'Price List Updated'[Highest Price], 99999999999999 )

RETURN

IsItemInPriceLevel

)

)

RETURN

CALCULATE (

[Spend @ actual],

KEEPFILTERS ( ItemsInPriceLevel ),

NOT ISBLANK ( Item_Master[Item #] )

)

)

[tkfisher]

Thanks, Ashish.

I will show a few images of example data to help out. First, here is the table that defines the Lowest and Highest Price for a Price Level. This is compared to the ASP of the Item Number. The intent would be for the overall ASP of the item to fit into one of these 10 levels. I have filtered to just one Item Number for simplicity.

Second, here is the table for the Item Master. I am only using this in my calculation to filter out any item that is blank, or not found in the first table. 

Lastly, here is the sales table. As you can see, multiple sales rows for the item example. This is where spend@actual and ASP (calculated) are derived from. 

The result I'm getting back should show me the total revenue based on where each Item Number fits within the Price Levels. It works when looking at the materials themselves, but the total for Price Level 1 does not add up to the sum of all of the Item Numbers (see below). Is this related to a specific filter I am identifying in my DAX? I have included my DAX below the image.

Revenue Segmentation = SUMX(VALUES('Price List Updated'[Item Number]),

var ItemsInPriceLevel = FILTER(ALLSELECTED('Price List Updated'),

var SalesOfItem = 'sales'[Spend @ actual]

var ItemASP = sales[ASP (calculated)]

var PriceLevelOfItem = FILTER('Price List Updated',NOT ISBLANK(ItemASP) && 'Price List Updated'[Lowest Price] <= ItemASP && IF(ISBLANK('Price List Updated'[Highest Price]), 999999999999999999999999999999 > ItemASP, 'Price List Updated'[Highest Price] > ItemASP))

var IsItemInPriceLevel = NOT ISEMPTY(PriceLevelOfItem)

return IsItemInPriceLevel)

return CALCULATE(sales[Spend @ actual] ,KEEPFILTERS(ItemsInPriceLevel), Item_Master[Item #] <> BLANK()))