Dynamic Segmentation Problem statement

[Shwetabh147]

We are working on critical issue on power bi requirement, still not able to find the solution for the below query.

Note~ We are attaching the required PBI file, kindly refer for the details.

Input - We have 3 tables in power bi model,

Sales_Fact table

Product table

Customer table

1) Sales Table -

Ean	Customer_key	week-st_dt
141	1	2/1/2021
141	2	2/2/2021
141	3	2/3/2021
141	4	2/4/2021
141	5	2/5/2021
141	6	2/6/2021
141	7	2/7/2021
141	8	2/8/2021
141	9	2/9/2021
141	10	2/10/2021

We have created 2 additional manual tables for displaying product and store segment in the report.

ID	Product Segment
1	High Share-High Growth
2	High Share-Low Growth
3	Low Share-High Growth
4	Low Share-Low Growth

ID	Store Segment
1	High Share-High Growth
2	High Share-Low Growth
3	Low Share-High Growth
4	Low Share-Low Growth

Explanation -
In order to achieve this we created summarize table (SUMMARIZE Function) for both store and product bucketing.

For Eg: We picked a particular EAN, brought in all the numerical field associated to that EAN, a

We have created 2 calculated measure in our summary table which are SALES_Growth and GSV Share %.

Product & Stores are ranked based on these two measures and then finally we created bucket based on the below logic –

Store Bucket Logic –

1)For Top 25percentile of the total records, Store-Bucket -High Sales High Growth is labelled to the customer_key.

2)For bottom 25percentile of the total records, Store-Bucket -Low Sales Low Growth is labelled to the customer_key.
3)If above two condition are not valid ,then check if Sales Growth  > Share%, THEN our Store-Bucket - High Sales Low Growth is labelled to the customer_key .
4)If first 2  condition are not valid ,then check if Sales Growth < Share%, THEN our Store-Bucket - Low Sales High Growth is labelled to the customer_key.

Same logic is used for Product Segmentation Bucket logic.

To achieve this above requirement, we have created DAX measure which are attached at the last.

Requirement –

We have 2 report slicer which has the store and product segmentation/bucket. And also we have table that has following columns required.



We want to figure out the above table-
EAN/SKU - This are Product SKU/Ean names in each row of the table selected from the above product segment slicer 1.
Total Store Present  - Count of total store for selected store segment from the above slicer 2.

No. Of Store-Present - Count of store out of Total store (store for that selected store bucket), where the product is selling.

Tried Approaches –

• Calculated tbl - Created Calculated table for the segmentation to create summarize view of the table in the data model itself. So that relationship can be build between the 2 table with our fact table and used for count calculation as required above.

            Issue – This created as a static snapshot of a summarized data in out both the tables, which resulted to static ranking and bucketing of our records for both the tables created. Hence, this approach could not gave the dynamic ranking to our data s per the report filter used in report.

• Measure Creation – Using the DAX code, we tried to create summarized table for both store & product separately. But could not establish the relation between this 2 table due to measure limitations.

Used Dax Measure –

STORE-SEGMENT CODE

Store Segment Count test =

Var Basictable =

FILTER(SUMMARIZE(ALLSELECTED(vw_customer),

     vw_customer[customerkey],

        "total sales" , [RGM Sales $],

        "LYRSV" , [LY RGM Sales $],

        "GSV",[Gross Sales Value],

        "Flag",1

       ),  not ISBLANK([RGM Sales $]) && not ISBLANK([LY RGM Sales $]) && vw_active_stores[StoreActiveFlag] = 1

)

VAR gsv1 = SUMX(Basictable,[GSV])

VAR MynskTable =

ADDCOLUMNS

       (Basictable,

           "NSK GSV Share",

               DIVIDE([Gross Sales Value],gsv1)

          )

var mynsktable2= ADDCOLUMNS(MynskTable,"Sales Growth", DIVIDE(([RGM Sales $]-[LY RGM Sales $]),[LY RGM Sales $]))

VAR MySummaryTable =

ADDCOLUMNS

        (mynsktable2,

            "Rank_Sales",

                RANKX(MynskTable2,[NSK GSV Share],,DESC,Dense),

            "Rank_Growth",

                RANKX(mynsktable2,[Sales Growth],,DESC,Dense))

var TotalStores = COUNTROWS(MySummaryTable)

VAR MyFinalTable =

        ADDCOLUMNS (

            MySummaryTable,

            "Bucket",

                IF (

                    [Rank_Sales] > totalStores * 0.75

                        && [Rank_Growth] > totalStores * 0.75,

                    4,

                    IF (

                        [Rank_Sales] < ROUNDDOWN(totalStores * 0.25,0)

                            && [Rank_Growth] < ROUNDDOWN(totalStores * 0.25,0),

                        1,

                        IF (

                             [Rank_Sales] <= [Rank_Growth] ,                           

                             2,

                            IF (

                                [Rank_Sales] > [Rank_Growth]  ,

                                3

                            )

                            )

                        )

                    )

                )

RETURN

COUNTROWS(FILTER(MyFinalTable,[customerkey] = SELECTEDVALUE(RGM[customerkey]) && [Bucket]=SELECTEDVALUE('Store Segmentation'[ID])))

2) Product Segment Count Code -

Product Segment test =

var Basictable =

FILTER(SUMMARIZE(ALLSELECTED(RGM),

     RGM[ean],

        "total sales" , [RGM Sales $],

        "LYRSV" , [LY RGM Sales $],

        "GSV",[Gross Sales Value],

        "Flag",1

       ),  not ISBLANK([RGM Sales $]) && not ISBLANK([LY RGM Sales $])

)

VAR gsv1 = SUMX(Basictable,[GSV])

VAR MynskTable =

ADDCOLUMNS

       (Basictable,

           "NSK GSV Share",

               DIVIDE([Gross Sales Value],gsv1)

          )

var mynsktable2= ADDCOLUMNS(MynskTable,"Sales Growth", DIVIDE(([RGM Sales $]-[LY RGM Sales $]),[LY RGM Sales $]))

VAR MySummaryTable =

ADDCOLUMNS

        (mynsktable2,

            "Rank_Sales",

                RANKX(MynskTable2,[NSK GSV Share],,DESC,Dense),

            "Rank_Growth",

                RANKX(mynsktable2,[Sales Growth],,DESC,Dense))

var totalStores = COUNTROWS(MySummaryTable)

VAR MyFinalTable =

        ADDCOLUMNS (

            MySummaryTable,

            "Bucket",

                IF (

                    [Rank_Sales] > totalStores * 0.75

                        && [Rank_Growth] > totalStores * 0.75,

                    4,

                    IF (

                        [Rank_Sales] < ROUNDDOWN(totalStores * 0.25,0)

                            && [Rank_Growth] < ROUNDDOWN(totalStores * 0.25,0),

                        1,

                        IF (

                             [Rank_Sales] <= [Rank_Growth] ,                            

                             2,

                            IF (

                                [Rank_Sales] > [Rank_Growth]  ,

                                3

                            )

                            )

                        )

                    )

RETURN

COUNTROWS( FILTER( MyFinalTable, [ean] = SELECTEDVALUE(RGM[ean]) && [Bucket] = SELECTEDVALUE('Product Segmentation'[ID])))

[Anonymous]

Hi @Shwetabh147,

From you description, some of the fields related to sales data in your measure are not clear.

For better understanding and testing it for you, please upload your sample pbix file.

Please be careful not to include sensitive information or anything unrelated to the issue.

Thank you for your understanding! 

[AllisonKennedy]

@Shwetabh147  did you say you have a sample pbix file you can share a link to please? 

If you want this to be dynamic based on report filters, it will need to be done with measures, but you'll need to provide the row context within the measure calculation. 

I think you're looking for a variation of this approximate lookup: https://excelwithallison.blogspot.com/2020/06/dax-approximate-lookup.html method, but your solution needs to go a step further. 

Once you follow the instructions in the above blog, you'll need to create a visual level filter measure that uses an Iterator function: Go to DAX.guide and choose 'Iterator' from the Any Attribute drop down to see your choices: 

My brain is thinking to use 

[Filter within Selected Segment] = 
COUNTROWS ( 

FILTER( MyTableName, [MyApproximateLookupValue] = SELECTEDVALUE ( SegmentTableName[ColumnName] ) )

)

then add that as a visual level filter to any visual that is on the page.