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
Super User
4 years ago

Hi,

Share some data and show the expected result.

tkfisher's avatar
tkfisher
Frequent Visitor
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 #] )
)
)