Segmentation via new table and a measure as a lookup

[Fenerbahce1907]

Hi everyone, 

I came across many usefull videos and posts regading group segmentation. However, none of them help me case entirely. 

Here's my situation: 

(1) I have a [Sales] table that contains sales data for each shop per day and product like this:

shop ID     DAY           PRODUCT      PRICE     QUANTITY     REVENUE

      2          21Dec19     1                    4                5                 20

      2          22Dec19     1                    4                5                 20

      2          23Dec19     1                    4                5                 20

      2          21Dec19     2                    5                5                 25

      2          22Dec19     2                    5                6                 30

      1          21Dec19     1                    4                5                 20

      1          22Dec19     1                    4                5                 20

      1          23Dec19     1                    4                5                 20

      1          21Dec19     2                    5                5                 25

      1          22Dec19     2                    5                6                 30

....

(2) I created a new table with brackets like this: 

CATEGORY      MIN     MAX 

0-400               0          400

401 - 600        400      600

>600               600     99999

where Category denots average sales / day /  shop

(3) I have a measure showing me the average sales / day / shop via the following formula in a measure inside the [Sales] Table

 AVG SALES PER SHOP PER DAY = CALCULATE(SUM(Sales Table [REVENUES]) / COUNTROWS( SUMMARIZE( Sales Table;  Sales Table [Shop ID]; Sales Table [Date]) ) )

What I want to achieve: 

Revenues per bracket like this: 

CATEGORY      REVENUE      

0-400               1231231          

401 - 600        2342423424      

>600               1322131     

My problem is that I cannot match the data with the "new table" containing the brackets as I have a no relationship to establish. 

Also, I cannot use a proper dax formula where I use a column value and search it inside the brackets. I need to be able to search a measure value inside the brackets. 

I am looking forward to your help. 

Cheers, 

[v-lionel-msft · Accepted solution]

Hi @Fenerbahce1907 ,

In this table, does the [REVENUE] column represent "sum of [REVENUE]"?

I created a measure like this:

Measure = 
CALCULATE(
    SUM(Sheet5[ REVENUE]),
    FILTER(
        Sheet5,
        [AVG SALES PER SHOP PER DAY] >= MAX(Sheet7[MIN ]) && [AVG SALES PER SHOP PER DAY] <= MAX(Sheet7[ MAX ])
    )
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fenerbahce1907]

Many thanks!!! 

Works like a charm 😉

[TomMartens]

Hey @Fenerbahce1907 ,

please consider to provide a pbix with sample data, upload the pbix and also the xlsx, if you use xlsx to create the sample data, to onedrive or dropbox and share the link. The tables you provided can not be copied to Power BI.

I do not fully understand what, the funny values in the revenue column of the final table (your expected result) represent. Is this sum of all the daily averages, from all the stores that are assigned to this bucket?

Please explain why your case is different from this pattern, as I currently see no difference: https://www.daxpatterns.com/dynamic-segmentation/

Regards,

Tom

[Fenerbahce1907]

Hi TomMartens

thanks for your prompt reply. 

Due to privacy reasons, I am not able to provide a pbix or xlsx file, but I am happy to explain in the best way possible my case thanks to your link as follows: 

My case from https://www.daxpatterns.com/dynamic-segmentation/ 

is different in that I want to check if a measure value fits inside the brackets. 

On the website https://www.daxpatterns.com/dynamic-segmentation/ the code checks for a column value inside the brackets: 

SalesRange :=

CALCULATE (

    [SalesAmount],

    FILTER (

        VALUES ( Sales[Price] ),  // THIS SHOULD BE A MEASURE VALUE IN MY CASE

        COUNTROWS (

            FILTER (

                Ranges,

                Sales[Price] >= Ranges[Min Price]

                && Sales[Price] < Ranges[Max Price]

            )

        ) > 0

    )

)

All tutorials on the web present a case like the formula above, where they check if a column value is inside a bracket in a new table. I want to check if a measure value is inside the brackets. That's my problem here. 

Hope this clarifies the situation a bit more.

Regards,

[TomMartens]

Hey @Fenerbahce1907 ,

this explains what makes it different, please provide sample data, that still represents your data model, please consider the effort as this will help all of us and will avoid futile discussions due to misunderstandings.

In the meantime, you might want to check this: https://community.powerbi.com/t5/Desktop/Grouping-a-count-of-specific-matching-data-between-2-tables...

Regards,

Tom