Get Percentage and Use it as a Sliding Slicer

[sheap069]

Hello,

I want to have two slicers interact in the following way.

Ignoring the numbers for the sliding slicer currently shown, as ideally I would like it to be 0 to 100% 

I have Applications and Groups. For a chosen Country and for a percentage of applications that come from that country, I want to display the data for the Groups that fit this criteria.

For example, if Group 1 has 6 applications, and 4 come from Canada and the other 2 come from France, the percentage of applications in Canada is 67%. 

Group 2 has 4 applications, with 2 from Canada and 2 from France, so the percentage of applications is 50%.

If I choose Canada in the first slicer, then choose the range from 60%-100%, only the data for Group 1 should appear in my report.

My data is structured as follows: 

Table 1

Application	Country
1	Canada
2	France
3	France
4	Canada
5	Canada
6	Canada
7	France
8	France
9	Canada
10	Canada

Table 2

Application	Group
1	1
2	1
3	1
4	1
5	1
6	1
7	2
8	2
9	2
10	2

I don't know how to go about doing this so that the percentage range is in that second slicer.

Thank you very much.

[sheap069]

Hello @AllisonKennedy @Icey 

Are you able to access the data set now?

Thanks,

[Icey · Accepted solution]

Hi @sheap069 ,

Please check:

1. Create a What-if Parameter.

2. Create a country table for slicer.

Country = DISTINCT(Applications[Country])

3. Create relationships.

4. Create a measure.

Measure = 
VAR t =
    ADDCOLUMNS ( 'Groups-Apps', "Country_", RELATED ( Applications[Country] ) )
VAR SelectedCountry =
    SELECTEDVALUE ( Country[Country] )
VAR Count_ =
    COUNTROWS ( t ) + 0
VAR Count_SelectedCountry =
    COUNTROWS ( FILTER ( t, [Country_] = SelectedCountry ) ) + 0
VAR Percent_ =
    DIVIDE ( Count_SelectedCountry, Count_, 0 )
RETURN
    IF (
        Percent_ >= MIN ( Percentage[Percentage] )
            && Percent_ <= MAX ( Percentage[Percentage] ),
        1
    )

5. Put the measure in needed visuals' "Filters on this visual" and set as " is 1".

PS: Based on the data you provided, group 1 meets your requirements, too. Please let me know, if there is any misunderstanding.

Best Regards,

Icey

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

[sheap069]

@Icey Perfect, I have this working the way I want it to!

Thank you very much again.

[sheap069]

Hello @AllisonKennedy 

I would like to follow up on this topic - did you get a chance to review my updated PBIX file with the three tables I'm using? I still need to be displaying the columns within Groups as you brought up.

Thank you.

[AllisonKennedy]

@sheap069

Can you make a mock up of what you want in Excel? It's been a while since I looked at this, but I don't see any differences between the latest file you attached and what I sent through before so please just summarize what isn't working yet and what you still need.

[sheap069]

Hi @AllisonKennedy 

Okay, I've attached an Excel sheet here of the model and the desired solution.

As you've demonstrated, for Canada being selected with a range of 60%-100%, Group 3 meets the criteria. I would just need the data from column three to be in the output as a table, which I've made in the last tab of the sheet.

Thank you

[Icey]

Hi @sheap069 ,

I don't have access to your file. Please give us access.

Best Regards,

Icey

[sheap069]

@Icey Hello,

Let me know if the file is working now here. 

Regards

[sheap069]

Hello @AllisonKennedy @Ashish_Mathur I am writing to follow up on this. Have you been able to view the updated data set? 

Thank you very much.

[AllisonKennedy]

Hi @sheap069 

Sorry for the delay, it has been a busy week. Please see attached file and let me know what changes you need. The attached only shows the data for the selected country, if you need to see all data for that group it will get more complex. 

Final step would be to filter the visual to show only when 'Applications in Selected Percent Range' = "yes"

Cheers!

[sheap069]

Hi @AllisonKennedy ,

No worries. Thank you for the PBIX. Yes, unfortunately I will need tables displaying information about the Applications and the Groups.

So I have three tables:

Application (data about Applications)

Group-App (to associate the Group ID with the App ID)

Group (data about Groups)

The data tables for Applications and Groups will be on other pages of my report.

I'm sending an updated PBIX so you can visually see the three tables and how they interact.

Thanks again,

Updated PBIX with Tables 

[sheap069]

Hello, @AllisonKennedy @Ashish_Mathur My apologies for the inconvenience. I am attaching the example PBIX file here with the updated data set in the Groups table.

And yes, one application ID will be associated to one single country.

Thank you again for your help.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[sheap069]

Hi @Ashish_Mathur 

Thank you for the PBIX. However, I forgot to mention that unfortunately the RELATED isn't working for me because an Application ID can actually be associated with more than one Group ID, so I'm not sure how to deal with this. 

Regards.

[AllisonKennedy]

@sheap069
Can you please update your sample data (either in the pbix file already shared with you or as sample tables we can copy/paste)
It shouldn't be a problem if you have many groups per application, provided you still have 1 application ID per country? You need to be able to have a 1 to many relationship between the two tables: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

[Ashish_Mathur]

Well, you obviously have not thought through before posting your dummy dataset.  I cannot help you if you cannot share a proper dataset.

[AllisonKennedy]

Why do you have two tables? Is the relationship 1 to 1? You will need to create a calculated column for percent in order to use it in a slicer, so you can either use the RELATED function to get Group into table 1 and calculate percent, or merge the two tables in Power Query as 1 table.

This means your grand total for percent calculations must be the same for each row - either Group total, Country total, or Grand total.