cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fenerbahce1907
Frequent Visitor

Segmentation via new table and a measure as a lookup

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, 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
v-lionel-msft
Community Support
Community Support

Hi @Fenerbahce1907 ,

c6.PNG

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 ])
    )
)

c7.PNGc8.PNG

 

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.

View solution in original post

5 REPLIES 5
v-lionel-msft
Community Support
Community Support

Hi @Fenerbahce1907 ,

c6.PNG

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 ])
    )
)

c7.PNGc8.PNG

 

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.

View solution in original post

Many thanks!!! 

 

Works like a charm 😉

 

 

TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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,

 

 

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors