Dynamic segmentation over two dimensions

[Deston]

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!

[Greg_Deckler]

@Deston - 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.

[Deston]

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]

Hi @Deston 

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.

[Deston]

Hello @v-diye-msft ,

A model and downlink was provided in the original post, however, happy to provide it again here:

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

Please let me know if you can download the Power BI report.

Thank you,

Deston