Forum Discussion

tkfisher's avatar
tkfisher
Frequent Visitor
4 years ago
Solved

Segmentation Help

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 numb...
Need Help
  • tkfisher's avatar
    tkfisher
    4 years ago

    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 #] )
    )
    )
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
4 years ago

Hi,

Share some data and show the expected result.

tkfisher's avatar
tkfisher
Frequent Visitor
4 years ago

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