Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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.
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.
Many thanks!!!
Works like a charm 😉
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
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:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |