Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Bari
Regular Visitor

Store slicer selections from a user and compare the selection against others

GroupSub-GrpCountryCityCost
SICDOUSATallahassee118.02
SICDOGermanyBerlin73.22
SICDOItalyTrieste39.08
SICDOItalyCosenza27.09
SICDOFranceStrasbourg67.33
SICDOIndiaBengaluru18.32

 

In power BI, there will be 4 slicers, Group, Track, Country and City. If an user selects SI, CDO, USA and Tallahassee, cost 118.02 will be shown on a card. Now, I need to show in another table visual what are the best 3 lowest cost city compared to the user selection and show the % difference. Output should look like:

GroupSub-GrpCountryCityActual CostUser selected Cost% diff
SICDOIndiaBengaluru18.32118.0284.48%
SICDOItalyCosenza27.09118.0277.05%
SICDOItalyTrieste39.08118.0266.89%

Please suggest how to achieve this.

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

Hi  @Bari ,

 

Here are the steps you can follow:

1. Create calculated table.

Create a slicer table that has no relationship to the main table.

Table 2 =
SUMMARIZE(
    'Table','Table'[Group],'Table'[Sub-Grp],'Table'[Country],'Table'[City])

vyangliumsft_0-1695701233200.png

2. Create measure.

selected Cost =
var _selectgroup=SELECTEDVALUE('Table 2'[Group])
var _selectsubgroup=SELECTEDVALUE('Table 2'[Sub-Grp])
var _selectcountry=SELECTEDVALUE('Table 2'[Country])
var _selectcity=SELECTEDVALUE('Table 2'[City])
return
SUMX(
FILTER(ALL('Table'),
'Table'[Group]=_selectgroup&&'Table'[Sub-Grp]=_selectsubgroup&&'Table'[Country]=_selectcountry&&'Table'[City]=_selectcity),[Cost])
Flag =
var _selectgroup=SELECTEDVALUE('Table 2'[Group])
var _selectsubgroup=SELECTEDVALUE('Table 2'[Sub-Grp])
var _rank=
RANKX(
    FILTER(ALL('Table'),
'Table'[Group]=_selectgroup&&'Table'[Sub-Grp]=_selectsubgroup),CALCULATE(SUM('Table'[Cost])),,ASC)
return
IF(
    _rank<=3,1,0)
% diff =
DIVIDE(
    [selected Cost]-SUM('Table'[Cost]),[selected Cost])

3. Place [Flag]in Filters, set is=1, apply filter.

vyangliumsft_1-1695701233201.png

4. Result:

vyangliumsft_2-1695701308319.png

 

 

Best Regards,

Liu Yang

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

7 REPLIES 7
v-yangliu-msft
Community Support
Community Support

Hi  @Bari ,

 

Here are the steps you can follow:

1. Create calculated table.

Create a slicer table that has no relationship to the main table.

Table 2 =
SUMMARIZE(
    'Table','Table'[Group],'Table'[Sub-Grp],'Table'[Country],'Table'[City])

vyangliumsft_0-1695701233200.png

2. Create measure.

selected Cost =
var _selectgroup=SELECTEDVALUE('Table 2'[Group])
var _selectsubgroup=SELECTEDVALUE('Table 2'[Sub-Grp])
var _selectcountry=SELECTEDVALUE('Table 2'[Country])
var _selectcity=SELECTEDVALUE('Table 2'[City])
return
SUMX(
FILTER(ALL('Table'),
'Table'[Group]=_selectgroup&&'Table'[Sub-Grp]=_selectsubgroup&&'Table'[Country]=_selectcountry&&'Table'[City]=_selectcity),[Cost])
Flag =
var _selectgroup=SELECTEDVALUE('Table 2'[Group])
var _selectsubgroup=SELECTEDVALUE('Table 2'[Sub-Grp])
var _rank=
RANKX(
    FILTER(ALL('Table'),
'Table'[Group]=_selectgroup&&'Table'[Sub-Grp]=_selectsubgroup),CALCULATE(SUM('Table'[Cost])),,ASC)
return
IF(
    _rank<=3,1,0)
% diff =
DIVIDE(
    [selected Cost]-SUM('Table'[Cost]),[selected Cost])

3. Place [Flag]in Filters, set is=1, apply filter.

vyangliumsft_1-1695701233201.png

4. Result:

vyangliumsft_2-1695701308319.png

 

 

Best Regards,

Liu Yang

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

 

@v-yangliu-msft one more question for you. I have different groups and sub-groups for the same countries and cities in my data. How to keep the slicer selection for group and sub-group constant and show the ranking of city based on the slicer selection of group and sub-group?

@v-yangliu-msft Thank you so much for your help. It worked.

Bari
Regular Visitor

Hi @JoeBarry , both the costs are there in the same table. If an user selects one city, we should be able to calculate the difference in cost with the other cities in that table.

VAR _SelCost = SUM(Table1[Cost])
VAR _Cost = SUM(Table1[Cost)

 

JoeBarry
Solution Sage
Solution Sage

Hi @Bari 

 

For the 2nd visual, turn off the interaction between the Country and City and the 2nd table

 

Highlight the Country Slicer, in the ribbion a Format tab will appear. Click on Edit interactions. While the slicer is highlighted, turn off the interaction in the 2nd table by clicking on this icon 

JoeBarry_0-1695366856821.png

 repeat for the city slicer.

 

Within the table open the filter pane right. Within the cost part go to filter type and choose Top N and add the Cost measure to the By value field. Then in Show items choose bottom and enter 3 in the empty field and then apply

 

This should do it for you.

 

Thanks

Joe

 

If this post helps, then please Accept it as the solution

Hi @JoeBarry , thank you for your reply. I alredy turned off the interaction. The issue is I could not store the user selected value as a reference point in my table which is 118.02 in the example. and then I need to show the dirrence between 118.02 and 18.32 which is 84.48%. When I a trying to place the user_selected_value measure in the table, instead of 118.02 it is giving me 18.32 only for Bangalore.

This will work if one City is filtered.

 

Create a measure 

 

Cost Difference % = 
VAR _SelCost = SUM(Table1[Cost])
VAR _Cost = SUM(Table2[Cost)
VAR _Diff = _SelCost - _Cost

RETURN
DIVIDE(_Diff , _SelCost)

 

Convert to % and add to Visual

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.