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.
I need a report where user will select a store and show the metrics for the other stores in that region in a separate visual.
Table: Store_Alignment
Store # | Region |
1 | South |
2 | South |
3 | East |
4 | West |
5 | East |
6 | South |
Table: Monthly_Sales
Store # | Month | Sales |
1 | Feb | $1,000 |
2 | Feb | $1,200 |
3 | Feb | $2,100 |
4 | Feb | $1,700 |
5 | Feb | $900 |
6 | Feb | $800 |
1 | Mar | $1,100 |
2 | Mar | $900 |
3 | Mar | $1,000 |
4 | Mar | $800 |
5 | Mar | $750 |
6 | Mar | $1,200 |
The example store is #2 which is in the South. The expected results should be the total sales for all other stores in the South like below:
Store # | Region | Sales |
1 | South | $2,100 |
6 | South | $2,000 |
Total | $4,100 |
Solved! Go to Solution.
Hi @tb_mbisams_ap ,
Consider creating a table with no joins.
then please create a new measure:
Measure =
VAR __store =
SELECTEDVALUE ( 'Table'[Store #] )
VAR __region =
SELECTEDVALUE ( 'Table'[Region] )
VAR __table =
CALCULATETABLE(
VALUES ( 'Table'[Store #] ),
FILTER (
ALL ( 'Table' ),
'Table'[Region] = __region
&& 'Table'[Store #] <> __store
)
)
VAR __filter =
IF ( MAX ( 'Monthly_Sales'[Store #] ) IN __table, 1 )
RETURN
__filter
use the measure as the table visual's filter:
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi @tb_mbisams_ap ,
Consider creating a table with no joins.
then please create a new measure:
Measure =
VAR __store =
SELECTEDVALUE ( 'Table'[Store #] )
VAR __region =
SELECTEDVALUE ( 'Table'[Region] )
VAR __table =
CALCULATETABLE(
VALUES ( 'Table'[Store #] ),
FILTER (
ALL ( 'Table' ),
'Table'[Region] = __region
&& 'Table'[Store #] <> __store
)
)
VAR __filter =
IF ( MAX ( 'Monthly_Sales'[Store #] ) IN __table, 1 )
RETURN
__filter
use the measure as the table visual's filter:
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi @tb_mbisams_ap then you need simple measure Sales=SUM(Monthly_Sales[Sales]).
In visual table, include Store column and this measure. Share feedback.
Proud to be a Super User!
Hi @tb_mbisams_ap just create relationship between two table, by Store column
Proud to be a Super User!
I don't think I explained enough. These tables already have a relationship, there are other tables with other store metrics as well. The user enters selects their store number at the top and sees various visuals around metrics exclusive to their store and then has a another visual that shows the sales for other stores in their region.
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 |
---|---|
43 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
58 | |
50 | |
44 | |
21 | |
19 |