Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Dynamic segmentation over two dimensions

Hello community

 

Hoping someone can assist. 

 

I've a unique scenario where I am attempting to dynamically segment the price difference between two products. My model has 2 product dimensions, and this allows me to compare, in one visual, the performance of a specific product (Product Dim 1), to another product (Product Dim 2). However, this does mean that I cannot use the traditional method of a single Product table (as per https://www.daxpatterns.com/) , and so I thought to use a virtual table instead. However, I'm struggling to get this to work:

 

Price Segmentation (Dynamic) =
VAR ProductsInSegment =
    FILTER (
        CALCULATETABLE (
            // An attempt at creating a virtual table
            ADDCOLUMNS (
                SUMMARIZE ( Sales, 'Date'[Week Ending] ),
                "Price Diff", [Price Difference]
            ),
            ALLSELECTED ( 'Product' ),
            ALLSELECTED ( 'And Product' )
        ),
        VAR PercentageDifference = [Price Diff]
        VAR SegmentForProduct =
            FILTER (
                'Percentage Buckets',
                IF (
                    PercentageDifference >= 0,
                    AND (
                        PercentageDifference >= 'Percentage Buckets'[Min],
                        PercentageDifference < 'Percentage Buckets'[Max]
                    ),
                    AND (
                        PercentageDifference <= 'Percentage Buckets'[Min],
                        PercentageDifference > 'Percentage Buckets'[Max]
                    )
                )
            )
        VAR IsProductInSegments =
            NOT ISEMPTY ( SegmentForProduct )
        RETURN
            IsProductInSegments
    )
VAR Result =
    CALCULATE ( SUM ( Summarize1[Volume Diff] ), KEEPFILTERS ( ProductsInSegment ) )
RETURN
    Result

 

Please follow this link to a report that illustrates the requirement in more detail:

 

https://1drv.ms/u/s!AiMOn9aJXFVYiEuADxfKXTgHdi_7?e=bR4ehZ

 

Any help would be greatly appreciated!

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion

    Anonymous - Just FYI I downloaded the file and am taking a look. Just so that I can understand in words what you are trying to do... OK, I have no idea, can you explain in words what you are trying to do? I see there are price buckets. I see that there is a volume difference. There are 2 products, do they both fall into the same price buckets or different price buckets. Are you trying to show that product 1 falls into price bucket A and has this much volume versus product 2 falls into price bucket B and has this other volume?

     

    Sorry, I'm not clear on this at all. Please @ me in any reply. Thanks.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hey Greg_Deckler 

      Thank you for replying. Here's what I am trying to do. We're a FMCG distribution company; we distribute products, but we also manufacture and distribute our own branded products. The aim of the model is to bucket theprice difference between the market related product and our own brand product, so that we can compare the volume difference within each bucket. 

      If you look at the 2 calculated tables, Summarize1 act as the harvesting table, where Summarize2 is where I bucket price difference to compare the volume difference. But, ideally this can all be done inside a measure so that can be fully dynamic. 

      Hope this makes sense 🙂 

      • v-diye-msft's avatar
        v-diye-msft
        Community Support

        Hi Anonymous 

         

        You might consider providing your dummy pbix that would be helpful for us to investigate it further. 

        You can upload it to the onedrive for business and share the link here.